Do I need to clean user input for DB::query calls

问题:

Reading the Laravel documentation I see that:

Note: The Laravel query builder uses PDO parameter binding throughout to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

Does that still apply if I only craft queries in the following manner?

DB::query("SELECT * from table WHERE id like " . $id);

回答1:

Let's take that sentence and emphasise the key phrase:

There is no need to clean strings being passed as bindings.

In your example, $id is not being passed as a binding, it is just being injected into the raw SQL, so it is not protected.

You should follow standard practice for preventing SQL injection:

• in cases like this, where the input is always an integer, you could use intval($id)
• you could get the underlying PDO object with DB::getPdo()/DB::getReadPdo() and use PDO::quote() to correctly escape strings
• although the documentation is rather poor, Laravel's DB facade can run fully parameterised queries, such as DB::select('SELECT * FROM users WHERE users.id = ?', array($userId));

Parameterised queries are usually considered the gold standard in injection prevention, and are what Eloquent is using internally when you use the query builder. The idea is that you first give the database (or, at minimum, the database driver) the complete query with no user input at all, so there is no doubt which tables and columns should be in use. You then pass in the user input as completely separate data, which is never actually written into the SQL, just applied to the query you already sent.

Parameterised queries can't do everything for you, though - for instance, most libraries, including PDO, can't bind a table or column name as a parameter. That's because it will actually create a different query every time it is run, negating the separation between query and data. If you want to do that, you therefore need some other method of ensuring safety - usually, a whitelist of allowed values is the best idea.

回答2:

No, DB::query() is not part of the concept of Query Builder. Instead, this will be protected:

DB::table('table')->where('id', 'like', $id)->get();

回答3:

But the best way to protect your queries is to use Query Builder at its max:

DB::table('table')->where('id', 'like', $id)->get();

Another way to protect your queries, if you really are forced to write a raw query, is to cast your data to the type they are supposed to be:

DB::query(DB::raw("SELECT * from table WHERE id like " . (int) $id));

In this case if $id is 'some exploit' the query will be:

SELECT * from table WHERE id like 0

In Query Builder you can also pass your parameters (bindings) like this to harden the security of your queries:

DB::select('SELECT * FROM users WHERE users.id = ?', array($userId));