2

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!

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
samyb8
  • 2,560
  • 10
  • 40
  • 68
  • Duplicate of [How prepared statements can protect from SQL injection attacks?](http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks/8265319#8265319) – Your Common Sense Apr 11 '13 at 14:42
  • @YourCommonSense any reason why you are not using the Close Vote dialog to supply possible duplicates? Providing them as comments only will not make these question go the review queue. – Gordon Apr 12 '13 at 06:26

3 Answers3

3

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).
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

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.

Royal Bg
  • 6,988
  • 1
  • 18
  • 24
  • 1
    Share your wisdom, instead of unconstructive comments:) I was told that you cannot use in prepared statements constants for example, if it's true, you cannot make your query with binding a constant from your code, which no one except you, will manupulate. Then you will need the standard way of making a query – Royal Bg Apr 11 '13 at 14:51
0

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...

STT LCU
  • 4,348
  • 4
  • 29
  • 47