How do you bind parameters to a query that isn't prepared?

Question

I'm making a small web application that will be receiving data input by users regularly. When researching how to make sure the data input is cleansed first, and for that it would seem prepared statements are the way to go.

I've found this SO question however, and as my application (at least as far as I know) won't be doing more than one query per page request, it would seem all I really need is the binding of values to parameters in the query.

I've been looking through the PHP manual on PDO and mysqli, but I can't find any examples where values are bound to a normal query. All the examples I've found have a $stmt->prepare somewhere prior to the binding.

Is whether or not the statement is "prepared" something that's determined by the support of the database, and the prepare statement will always be in the code? Or is there a way to bind parameters directly into a $dbh->query(...)?

To explain why I'm looking to see if its possible to not use prepare, is due to this statement from the SO question I linked earlier in the post:

When not to use prepared statements? When you're only going to be running the statement once before the db connection goes away.

When not to use bound query parameters (which is really what most people use prepared statements to get)?

and this

Personally I wouldn't bother. The pseudo-prepared statements are likely to be useful for the safe variable quoting they presumably provide.

Answer 1

How do you bind parameters to a query that isn't prepared?

You don't. An SQL string with parameters (i.e. question marks in specific places) needs to be parsed (i.e. prepared) first before those question marks can be treated as insertion points for parameter values.

Therefore you always need to call prepare() before you can call bind().

---

A parameterized statement is a string that contains SQL and placeholder markers (for example question marks, but different databases use different placeholders):

$sql = "SELECT user_id FROM user WHERE user_name = ?"

Now assume there's a value you want to insert at this location:

$_POST["username"]

Preparing a statement, broadly speaking, gives the question marks their special meaning "a value can be inserted here". In other words, it creates parameters from the placeholders.

$stmt->prepare($sql)

Binding a value to a parameter sets the parameter to a specific value.

$stmt->bind_param("s", $_POST["username"])

Now the query can be executed without the SQL string and the user-supplied value ever actually coming into contact with each other. This is the important bit: SQL and parameter values are sent to the server separately. They never touch each other.

$stmt->execute();

The advantages are:

• You can bind a new value to the parameter and execute the query again without having to repeat all of it (useful in a loop).
• SQL injection is impossible, no matter what value $_POST["username"] contains.

Answer 2

You don't. Here's why:

If using $dbh->query(...) you can just call SQL with the parameters interpolated into the SQL string. By using a query like

$dbh->query("INS INTO MY_TABLE_OF_NAMES ('$name');"); 

10 or so years ago, this is how most SQL was done. This is the most straightforward way of invoking the database, using the SQL interface already implemented by the RDMS, without the need for a special lower-level interface. But people discovered that this was dangerous because of something called SQL injection.

http://en.wikipedia.org/wiki/Sql_injection

The simplest and most common example goes something like this. Suppose you had an SQL call on your web page that would run:

 INS INTO MY_TABLE_OF_NAMES VALUE ('$name');

But then someone would come to your site and enter there name as bob'); DROP TABLE MY_TABLE_OF_NAMES;

Suddenly your interpolated SQL statement becomes

INS INTO MY_TABLE_OF_NAMES VALUE ('bob'); DROP TABLE MY_TABLE_OF_NAMES; );

Which would subsequently insert bob into your database, delete all of your names, and throw an error for the trailing ); when your website ran it.

So, prepared statements were invented. Instead of interpolating strings directly into your SQL, it will use ? characters to denote dynamic values, and a bind function is used to insert the string safely. This way malevolent input will never be interpreted as SQL code by your database engine and your site can't be tricked into doing things it doesn't want to do. The prepare command takes an SQL string takes a bit of SQL and semi-compiles it into a lower-level database language leaving spaces open of dynamic strings wherever a ? is used. Bind then fills one of those open spaces with a piece of data, encoded to escaped ASCII, so that it cannot be misinterpreted as SQL code. Once all of those ?s are filled, the SQL is ready to be sent to the RDMS to be run.

So to answer your question, you will never bind a parameter to a simple query. If you want dynamic variables in a simple query you will just interpolate them into the SQL string. But this is dangerous. Prepared statements allow you to precompile an SQL statement then safely bind dynamic parameters to it to create safe dynamic SQL. Binding to SQL is purely a construct of prepared statements.

Answer 3

In order to use bound parameters, you have to use prepared statements. This is just the way things are currently implemented in pdo and mysqli. I'm not sure if certain database products support some type of communication protocol where parametrized sql(sql text which uses placeholders) is sent along with the parameter values without having to first do an explicit prepare call, but pdo and mysqli don't expose this functionality if it's available. It would sure be a welcome feature to web apps.

With pdo, yes, whether or not the sql statement actually gets prepared when you call $dbh->prepare($sql) does depend on database support. pdo will emulate prepared statements when the database doesn't support them, or it can always emulate them if its configured to do so. In fact, pdo emulates prepared statements for the mysql driver by default, and has done so by default for a very long time. It emulates them by creating dynamic sql, quoting the values, just like you would. In this case, the sql(with the final values embedded into the text) is sent to the database when you call $stmt->execute(). Yes, sql injection is possible here under certain scenarios.