Avoid possible SQL injections when using Model::findFirst("column = '$value'")

Hi, I want to know if there is a way for phalcon to automatically parse the data inputs I'm sending to the model with the purpose to avoid possible SQL injections, for example if I use:

Model::findFirst("column = '$value'"); //This is very similar to the examples given in the Phalcon documentation

And $value has a single quote then Phalcon will show an SQL error because the quotes are not correctly closed and I think it will give problems with SQL injections.

Is there other way to set the query parameters using the example above? or do I need to do it manually?

Thanks-.



82.7k

If you use the ORM that way you're vulnerable to SQL injections, you have to use bound parameters to avoid them: http://docs.phalconphp.com/en/latest/reference/models.html#binding-parameters

Change it to:

Model::findFirst(["column = :value:",'bind'=>['value'=>$value]]);


10.6k

The documentation is very unclear on this. Or at least, badly worded. In one paragraph it says that Phalcon does this for you, then a few paragraphs later, it says you should use "bound paramaters" to safegurad against SQL injections. As a result I am still confused with what is safe and what isn't, so I tend to potencially waste time by over programming by preg_replacing (and other manual filtering) queries first, as I can't trust what is safe and what isn't. The DB side of things is probably the most complicated part of Phalcon, and there is no clear documentation for it.



2.9k
edited Jan '15

The documentation is very unclear on this. Or at least, badly worded. In one paragraph it says that Phalcon does this for you, then a few paragraphs later, it says you should use "bound paramaters" to safegurad against SQL injections.

I was under the impression Phalcon did this for you as well until reading this thread. But after reading the documentation again, I realized it's quite clear on this:

Bound parameters are also supported in Phalcon\Mvc\Model. Although there is a minimal performance impact by using bound parameters, you are encouraged to use this methodology so as to eliminate the possibility of your code being subject to SQL injection attacks.

This comes right after the section on filtering resultsets, so it means: when reading from the database, you should use bound parameters to avoid SQL injection. See Binding Parameters.

The part that may confuse beginners comes a little bit later under Creating/Updating Records:

Values assigned directly or via the array of attributes are escaped/sanitized according to the related attribute data type. So you can pass an insecure array without worrying about possible SQL injections:

Then under Avoiding SQL Injections:

Every value assigned to a model attribute is escaped depending of its data type. A developer doesn’t need to escape manually each value before storing it on the database. Phalcon uses internally the bound parameters capability provided by PDO to automatically escape every value to be stored in the database.

In other words: when saving to the database, you don't need to worry about using bound parameters because Phalcon does this for you.



10.6k

Pajamaman, That's pretty good detective work! The docs are a bit cryptic, but your translation makes it much more clear. Thanks. I completed my first Phalcon development never being completely sure on this point and so I was manually filtering for good measure.

So data going in is automatically filtered and sanitised. Queries/data coming out need to be bound. That's concise. Thanks again!