When should I use prepared statements?

by

tl/dr

Always. 100% of the time, use it. Always; and even if you don’t need to use it. USE IT STILL.

mysql_* functions are deprecated. (Notice the big red box?)

Warning This extension was deprecated in PHP 5.5.0, and it was removed
in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be
used. See also MySQL: choosing an API guide and related FAQ for more
information. Alternatives to this function include:

You’d be better off using PDO or MySQLi. Either of those 2 will suffice as compatible libraries when using prepared statements.

Trusting user input without prepared statements/sanitizing it is like leaving your car in a bad neighborhood, unlocked and with the keys in the ignition. You’re basically saying, just come on in and take my goodies enter image description here

You should never, and I mean never, trust user input. Unless you want this:

SQL Injection

In reference to the data and storing it, as stated in the comments, you can never and should never trust any user related input. Unless you are 101% sure the data being used to manipulate said databases/values is hard-coded into your app, you must use prepared statements.

Now onto why you should use prepared statements. It’s simple. To prevent SQL Injection, but in the most straight forward way possible. The way prepared statements work is simple, it sends the query and the data together, but seperate (if that makes sense haha) – What I mean is this:

Prepared Statements
Query: SELECT foo FROM bar WHERE foo = ?
Data:  [? = 'a value here']

Compared to its predecessor, where you truncated a query with the data, sending it as a whole – in turn, meaning it was executed as a single transaction – causing SQL Injection vulnerabilities.

And here is a pseudo PHP PDO example to show you the simplicity of prepared statements/binds.

$dbh = PDO(....); // dsn in there mmm yeahh
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name="one";
$value = 1;
$stmt->execute();

Taken from PHP Manual for PDO Prepared Statements

More Reading

More Related Contents:

  1. Why is using a mysql prepared statement more secure than using the common escape functions?
  2. How can I prevent SQL injection in PHP?
  3. SQL injection that gets around mysql_real_escape_string()
  4. How can I sanitize user input with PHP?
  5. Are PDO prepared statements sufficient to prevent SQL injection?
  6. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  7. Reference: What is a perfect code sample using the MySQL extension? [closed]
  8. The ultimate clean/secure function
  9. Do htmlspecialchars and mysql_real_escape_string keep my PHP code safe from injection?
  10. Examples of SQL Injections through addslashes()?
  11. SQL injections in ADOdb and general website security
  12. Is mysql_real_escape_string() broken?
  13. how safe are PDO prepared statements
  14. Is htmlspecialchars enough to prevent an SQL injection on a variable enclosed in single quotes?
  15. What does mysql_real_escape_string() do that addslashes() doesn’t?
  16. xss attack on a php page
  17. How can I store my users’ passwords safely?
  18. How to create a secure mysql prepared statement in php?
  19. Two-way encryption: I need to store passwords that can be retrieved
  20. Secure random number generation in PHP
  21. encrypt and decrypt md5
  22. How to sanitze user input in PHP before mailing?
  23. best practice to generate random token for forgot password
  24. Binding params for PDO statement inside a loop
  25. using nulls in a mysqli prepared statement
  26. store_result() and get_result() in mysql returns false
  27. How can I determine a file’s true extension/type programmatically?
  28. Mysqli Prepare Statement – Returning False, but Why? [duplicate]
  29. Can I serve MP3 files with PHP?
  30. How to run the bind_param() statement in PHP?

Leave a Comment