pallade's avatar

QueryBuilder: is "whereFulltext" safe with user content?

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."...

0 likes
3 replies
tisuchi's avatar

@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.

pallade's avatar

@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!

tisuchi's avatar

@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.