Are PDO prepared statements sufficient to prevent

Question

Let's say I have code like this:

$dbh = new PDO("blahblah");

$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

The PDO documentation says:

The parameters to prepared statements don't need to be quoted; the driver handles it for you.

Is that truly all I need to do to avoid SQL injections? Is it really that easy?

You can assume MySQL if it makes a difference. Also, I'm really only curious about the use of prepared statements against SQL injection. In this context, I don't care about XSS or other possible vulnerabilities.

Answer 1

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

Parameterised queries work by sending the code and the data separately, so it would never be possible to find a hole in that.

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

Answer 2

Prepared statements / parameterized queries are generally sufficient to prevent 1st order injection on that statement^*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd order injection.

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign ' characters or similar.

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

We see, then, that prepared statements are enough for a single query, but by themselves they are not sufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce that all access to a database within the application uses safe code. However, used as part of good application design — which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql — prepared statements are the primary tool for solving the Sql Injection problem. If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

---

_{^*It turns out that MySql/PHP are (okay, were) just dumb about handling parameters when wide characters are involved, and there is still a rare case outlined in the other highly-voted answer here that can allow injection to slip through a parameterized query.}

Answer 3

No this is not enough (in some specific cases)! By default PDO uses emulated prepared statements when using MySQL as a database driver. You should always disable emulated prepared statements when using MySQL and PDO:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Another thing that always should be done it set the correct encoding of the database:

$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

Also see this related question: How can I prevent SQL injection in PHP?

Also note that that only is about the database side of the things you would still have to watch yourself when displaying the data. E.g. by using htmlspecialchars() again with the correct encoding and quoting style.

Answer 4

Eaven if you are going to prevent sql injection front-end, using html or js checks, you'd have to consider that front-end checks are "bypassable".

You can disable js or edit a pattern with a front-end development tool (built in with firefox or chrome nowadays).

So, in order to prevent SQL injection, would be right to sanitize input date backend inside your controller.

I would like to suggest to you to use filter_input() native PHP function in order to sanitize GET and INPUT values.

If you want to go ahead with security, for sensible database queries, I'd like to suggest to you to use regular expression to validate data format. preg_match() will help you in this case! But take care! Regex engine is not so light. Use it only if necessary, otherwise your application performances will decrease.

Security has a costs, but do not waste your performance!

Easy example:

if you want to double check if a value, received from GET is a number, less then 99 if(!preg_match('/[0-9]{1,2}/')){...} is heavyer of

if (isset($value) && intval($value)) <99) {...}

So, the final answer is: "No! PDO Prepared Statements does not prevent all kind of sql injection"; It does not prevent unexpected values, just unexpected concatenation

Answer 5

No, they are not always.

It depends on whether you allow user input to be placed within the query itself. For example:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

would be vulnerable to SQL injections and using prepared statements in this example won't work, because the user input is used as an identifier, not as data. The right answer here would be to use some sort of filtering/validation like:

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];
$allowedTables = array('users','admins','moderators');
if (!in_array($tableToUse,$allowedTables))    
 $tableToUse = 'users';

$stmt = $dbh->prepare('SELECT * FROM ' . $tableToUse . ' where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

Note: you can't use PDO to bind data that goes outside of DDL (Data Definition Language), i.e. this does not work:

$stmt = $dbh->prepare('SELECT * FROM foo ORDER BY :userSuppliedData');

The reason why the above does not work is because DESC and ASC are not data. PDO can only escape for data. Secondly, you can't even put ' quotes around it. The only way to allow user chosen sorting is to manually filter and check that it's either DESC or ASC.

Answer 6

Personally I would always run some form of sanitation on the data first as you can never trust user input, however when using placeholders / parameter binding the inputted data is sent to the server separately to the sql statement and then binded together. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement.