Sql queries binding variables vs specifying them

Question

I am creating a simple query in mySql to insert some values from a form into my db.

My question is simple, but in reference to the difference between binding variables vs specifying them into the sql statement.

Binding:

$query = "INSERT INTO test (name, lastName, price) VALUES (:name, :lastName, :price)";
$apply = $con -> prepare($query);
$apply -> execute (array(':name'=>$name,':lastName'=>$lastName,':price=>$price'));

Typical:

$query = "INSERT INTO test (name, lastName, price) VALUES ($name, $lastName, $price)";
Execute the query....

Is the Binding option really recommended even for simple cases as the one above? Why is that?

Thanks!

Answer 1

Although you can have your query perfectly safe without binding (by formatting all variables manually), using prepared statements to represent your data in the query is indeed the only proper way.

The importance of using prepared statements is often misjudged, so, I'd like to clarify the real benefits:

• prepared statement makes proper formatting (or handling) inevitable.
• prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
  • some PHP 'magic' feature which rather spoils the data than make it safe.
  • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
• prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
• prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

Answer 2

Because it is more secure.

If the three variables are populated with user's data, you don't need to sanitize the inputs. Also, if you need to repeat that query multiple times, the overall execution should be faster.

And I personally think that it's easier to read and debug, when you have to read again your queries after a couple of months...

Answer 3

It's necessary since you have user defined values, and they can be easily manipulated by the user, to break your query structure and execute unwanted commands.

It wouldn't be necessary if the data is only manipulated by the server. But since it uses data from the superglobals, which user can manipulate (cookie, session, post, get, request...), it's a must.