Validating a search string for an SQL text search

Question

Is it safe to allow these special characters when validating a string? The user is currently allowed to send alpha numeric and the following special characters from the browser:

',.!&()_-

My main concern is code injection. I'm still learning Laravel and I understand it handles a lot security wise. I'd like to make sure I'm taking solid precautions for good security.

I have the following code which uses the PHP Laravel framework to validate on the server side:

$this->validate($request, [
 'str' => 'bail|required|regex:/^[a-zA-Z0-9 \',.!&_-]+$/u'
]);
$search_str = $request->str;
$query = Story::where('story_text', 'LIKE', '%'.$search_str.'%');

Is this sufficiently secure or is there more I should be doing?

Answer 1

Laravel's Eloquent uses PDO's parameterized queries, so you get the same level of protection from SQL injection attacks as using PDO directly. Which is to say, you don't generally need to worry about sanitizing your input with Laravel.

The main exception being if you ever use Eloquent's raw query. You should avoid passing user-supplied input to a raw query at pretty much any price, but that's where you'd need to sanitize.

As for the input validation, you should just be using it to make sure the inputs conform to your requirements. For instance, ensuring that something looks like an email or is alphanumeric. Or to make sure inputs correspond to an extant record in the database. It's almost never necessary to use it for input sanitization and will probably end up confusing both you and the user, later on.

Answer 2

I think the answer from @Siegen does good job of giving you guidance with regards to validation vs. SQL injection mitigation.

I however want to point out a concern with your design. Any time to find yourself trying to make a text search in the format of LIKE %{some value}% you should understand that this search will require a full table scan as the wildcard character at the beginning of the LIKE definition will disallow use of typical BTREE index on the field.

In these cases, you should strongly consider using a FULLTEXT index on your field(s) in question and use a natural language search against it using MATCH().

Here is MySQL Documentation for further reading.