User Contributed Notes 26 notes

To those wondering why adding quotes to around a placeholder is wrong, and why you can't use placeholders for table or column names:

There is a common misconception about how the placeholders in prepared statements work: they are not simply substituted in as (escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for "SELECT name FROM my_table WHERE id = :value" will be the same whatever you substitute for ":value", but the seemingly similar "SELECT name FROM :table WHERE id = :value" cannot be planned, because the DBMS has no idea what table you're actually going to select from.

Even when using "emulated prepares", PDO cannot let you use placeholders anywhere, because it would have to work out what you meant: does "Select :foo From some_table" mean ":foo" is going to be a column reference, or a literal string?

When your query is using a dynamic column reference, you should be explicitly white-listing the columns you know to exist on the table, e.g. using a switch statement with an exception thrown in the default: clause.

After a bunch of searching I've learned 2 things about prepared statements:1.) It fails if you enclose in a single quote (')This fails: "SELECT * FROM users WHERE email=':email'"This works: "SELECT * FROM users WHERE email=:email"2.) You cannot search with a prepared statementThis fails: "SELECT * FROM users WHERE :search=:email"This succeeds: "SELECT * FROM users WHERE $search=:email"

In my case I allow the user to enter their username or email, determine which they've entered and set $search to "username" or "email". As this value is not entered by the user there is no potential for SQL injection and thus safe to use as I have done.

Prepared statements only project you from SQL injection IF you use the bindParam or bindValue option.

For example if you have a table called users with two fields, username and email and someone updates their username you might run

UPDATE `users` SET `user`='$var'

where $var would be the user submitted text.

Now if you did <?php$a=new PDO("mysql:host=localhost;dbname=database;","root","");$b=$a->prepare("UPDATE `users` SET user='$var'");$b->execute();?>

and the user had entered User', email='test for a test the injection would occur and the email would be updated to test as well as the user being updated to User.

Using bindParam as follows<?php$var="User', email='test";$a=new PDO("mysql:host=localhost;dbname=database;","root","");$b=$a->prepare("UPDATE `users` SET user=:var");$b->bindParam(":var",$var);$b->execute();?>

The sql would be escaped and update the username to User', email='test'

With PDO_MYSQL you need to remember about the PDO::ATTR_EMULATE_PREPARES option.

The default value is TRUE, like$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES,true);

This means that no prepared statement is created with $dbh->prepare() call. With exec() call PDO replaces the placeholders with values itself and sends MySQL a generic query string.

The first consequence is that the call $dbh->prepare('garbage');reports no error. You will get an SQL error during the $dbh->exec() call.The second one is the SQL injection risk in special cases, like using a placeholder for the table name.

The reason for emulation is a poor performance of MySQL with prepared statements. Emulation works significantly faster.

Noteworthy in my opinion is that if you prepare a statement but do not bind a value to the markers it will insert null by default. e.g.<?php/** @var PDO $db */$prep = $db->prepare('INSERT INTO item(title, link) VALUES(:title, :link)');$prep->execute();?>Will attempt to insert null, null into the item table.

If an attacker pass '1;-- ' as input named 'search_for', he is not a very bad attacker; because he didn't delete your data! In the above example, an attacker can do anything with connected database (unless you have restricted the connected user). Unfortunately, as Simon Le Pine mentioned, you cannot use prepared statements as other parts of a query; just can be used to search in indexes.

Hope this helps from loosing some data.Sorry for my a bit weak English!

If you are only submitting one query, using PDO::query() with PDO::quote() is much faster (about 3x faster in my test results with MySQL). A prepared query is only faster if you are submitting thousands of identical queries at once (with different data).

If you Google for performance comparisons you will find that this is generally consistently the case, or you can write some code and do your own comparison for your particular configuration and query scenario. But generally PDO::query() will always be faster except when submitting a large number of identical queries. Prepared queries do have the advantage of escaping the data for you, so you have to be sure to use quote() when using query().

Many students are tempted to add single quotes around string place holders in the SQL statement, since that’s what they normally do around strings in SQL and PHP.

I have to explain:

Quotes are not part of the string — they are used to construct a string in the coding language. If you are creating a string literal in SQL or PHP, then it must indeed be quoted. If the string has already been created, and is being passed on, then additional quotes would be wrong at best, and mis-interpreted at worst.

In prepared place holders, think of place holders as variables, which, whether they are strings or other values, are always written without quotes.

Use prepared statements to ensure integrity of binary data during storage and retrieval. Escaping/quoting by f.e. sqlite_escape_string() or PDO::quote() is NOT suited for binary data - only for strings of text.

If you need to create variable sql statements in a prepare statement...for example you may need to construct a sql query with zero, one, two, etc numbers of arguments...here is a way to do it without a lot of if/else statements needed to glue the sql together:

It is possible to prepare in advance several statements against a single connection. As long as that connection remains open the statements can be executed and fetched from as often as you like in any order; their "prepare-execute-fetch" steps can be interleaved in whichever way is best.

So if you're likely to be using several statements often (perhaps within a loop of transactions), you may like to consider preparing all the statements you'll be using up front.

This Is A Secure Way To Sign in With pdo::prepare--------------------------------------------------------<?phpfunction secured_signin($username,$password){ try {$connection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbusername, $dbpassword);$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

beware
PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

Please note that the statement regarding driver_options is misleading:

"This array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns. You would most commonly use this to set the PDO::ATTR_CURSOR value to PDO::CURSOR_SCROLL to request a scrollable cursor. Some drivers have driver specific options that may be set at prepare-time"

>> The query cache is not used for server-side prepared statements before MySQL 5.1.17 <<

The MySQL query cache buffers complete query results and is used to satisfy repeated identical queries if the underlying tables do not change in the meantime - just what happens all the time in a typical web application. It speeds up queries by a several hundred to a several thousand percent.

Obviously, it doesn't make much sense to give up query caching for the relatively small performance benefit of prepared statements (i.e. the DBMS not having to parse and optimize the same query multiple times) - so using PDO->query() for SELECT statements is probably the better choice i you're connecting to MySQL < 5.1.17.