Hello, I'd like to know if the whereFulltext() function in the QueryBuilder is safe with user content. I am doing some testing, and I am receiving "syntax error" exceptions from the DB, for example, if I enter "+a*)" as search term I get this:
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected ')', expecting $end in .../site/vendor/illuminate/database/Connection.php on line 712
( ! ) Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected ')', expecting $end (SQL: select `someval` from `data` where match (`firstname`, lastname`) against (+a*) in boolean mode)) in .../site/vendor/illuminate/database/Connection.php on line 712
I am using it like this:
$results = Capsule::table('paper')
->select('someval')
->whereFullText('searchabletext', $query, ['mode' => 'boolean'])
->get();
I am not using full Laravel, only the QueryBuilder through the Capsule.
Is this safe, and I just need to catch the exception? The docs state that "There is no need to clean or sanitize strings passed to the query builder as query bindings."...
@pallade I haven't try whereFullText option yet.
But I can feel a potential issue if someone can manipulate your $queyr. I rather suggest to use this:
$query = strip_tags($query);
$query = filter_var($query, FILTER_SANITIZE_STRING);
$results = Capsule::table('paper')
->select('someval')
->whereRaw("MATCH (searchabletext) AGAINST (? IN BOOLEAN MODE)", [$query])
->get();
It will at least prevent from SQL Injection and Full Text Injection.
@tisuchi $query is supposed to come straight from the user, so it will definitely be open playing field for anybody... I need to sanitize it or make it safe... I have tried to do some SQL injection on myself and it does not seem to be working, but one can never be sure!
@pallade That's true. But by combining parameter binding and input validation and sanitization, you can ensure that your code is secure against both SQL injection and full-text injection.
Please or to participate in this conversation.