Full text searches and security

Hi. I need to utilize boolean full-text search in my application. As I knew from this post: https://forum.phalconphp.com/discussion/150/full-text-searches I have to utilize Raw SQL to achieve this.

How do I make my full text search secure?

Will it be sufficient protection from sql injection if I just cast request to string?

$this->request->getQuery('ftSearchQuery','string')


44.6k

If you are really concerned about SQL injection then you can turn off allowing sql literals for the entire project by setting enable_literals to false. This might be difficult at first as you would need to go through and bind values to mundane queries.

If you just want to be normal safe then use a parameter with :value: and then bind ftSearchQuery to that place holder. You can also bind the type if you are worried about some casting exploit.

Binding Parameters



6.2k
edited Jan '16

Hi, dschissler.

Not sure I understood first part with enable_literals. That's totally not a problem for me to use parameters in queries that can be presented in PHQL(that's how I usually do), however documentation states enable_literals affects only PHQL queries.

As I undersand from second part of your message, I can use raw sql for ftSearch with params, like:

$sql = 'SELECT field1, field2 FROM table WHERE MATCH (/*ft fields list*/) AGAINST(":param1: :param2:" IN BOOLEAN MODE)'
$params = [ 'param1'=>'firstValue','param2'=>'secondValue']
$result = new MyModel();
$results = new Resultset(null, $result, $result->getReadConnection()->query($sql,$params));

Is it possible to bind parameter types in this code? And if it's not possible, is this solution secure enough?



44.6k

Use PHQL. It supports what you are trying to do.



6.2k

Hm. I haven't found this to be mentioned in documentation. Out of the box at least. I found custom dialect function MATCH_AGAINST example here: https://blog.phalconphp.com It works with one parameter. I've tried to improve this sollution to pass many parameters separately and it's not working for me, not sure why.



6.2k
edited Jan '16

Ok. Finaly got this to work.



44.6k

There is also the incubator code for extending the DB adatper dialect.