We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Phalcon and prepared sql statements

Hello, I was looking for ways to improve my work with dynamic mysql database/table names. And I found prepare method in Phalcon\Db\Adapter\Pdo. Also I've read somewhere on forum that phalcon prepares sql statements implicitly.

So my first question: Why and when do we need to explicitely use prepare method?

And my second question: Is there a way to utilize phalcon prepared statements to achieve something like this:

SET @id := '47';
SET @table := concat(@id,'_2013_2014_voucher');
set @qry1:= concat('select * from ',@table);
prepare stmt from @qry1 ;
execute stmt ;

? (this is mysql implementation of dynamic table names usage from the stackoverflow post).



32.2k

Q1: always, it's the best wait prevent SQL injection

Q2: check this and then use the DB Layer to execute it

Good luck



125.7k

You should use prepared statements whenever the statement could potentially include text from a user. This includes usernames, email addresses... basically anything that wasn't 100% internally generated.

This is to prevent SQL injection. SQL injection happens when a query string is being interpreted by the database engine. It's literal, so it doesn't know that ;delete from users; was intended to be a username and not a new query. With prepared queries, the database engine parses the query ahead of time without any actual values, so it can't get confused. Then, when you have a malicious string of user text , such as ;delete from users;, the engine doesn't try to parse that string and just treats it as a string to match on.



15.9k
edited Apr '18

Hi, Emilio, Dylan. Thanks for your answers first of all.

I prety much got that prepared statements is a security requirement nowadays, however Q1 was more about why do i need to explicitely write this:

$statement = $db->prepare($some_sql_statement);

If phalcon prepares sql statements anyway. Or it doesn't do so on DB Layer? (hadn't a minute to find out in mysql logs yet)



125.7k

You only need to do that if you're using the DB layer directly. That DB layer is what Phalcon uses to talk to the database. Code like that essentially bypasses Phalcon and the work it would do.

If you're using the Phalcon ORM, you can do a statement like:

$Records = $Model::find(['conditions'=>'country=:country:','bind'=>['country'=>'Canada']);

That will translate to (roughly speaking - I don't know the functions exactly

$statement = $db->prepare('SELECT * FROM model_table where country = ?')
$db->executePrepared($statement,['Canada']);