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 straight forward way of invoking the database, using the SQL interface alreaady 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 a sql call in 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 string directly into your strings it will use ? chars to denote dynamic values, and a bind function is used to insert the string safely. This way manevolent input will never be interpreted as SQL code by your database engine and you site cant be tricked into doing things it doesnt want to do. The prepare command takes a sql string takes a bit of sql and semi compiles into a lower level database langauge leaving spaces open of dynamic strings wherever a ? is used. Bind then takes one of those open spaces and fills it 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 varables in a simple query you will just interpolate them into the SQL string. But this is dangerous. Prepared statements allow you to precompile a sql statement then safely bind dynamic parameters to it to create safe dynamic SQL. Binding to sql is purely a construct of prepared statements.