PHP MySQLI Prevent SQL Injection [duplicate]

by

Any query can be injected whether it’s read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with mysqli), which renders the intended query irrelevant.

Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example:

SELECT col1 FROM t1 WHERE col2 = ?

? is a placeholder for a parameter. Using mysqli, you can create a prepared statement using prepare, bind a variable (argument) to a parameter using bind_param, and run the query with execute. You don’t have to sanitize the argument at all (in fact it’s detrimental to do so). mysqli does that for you. The full process would be:

$stmt = $mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?");
$stmt->bind_param("s", $col2_arg);
$stmt->execute();

There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection:

$stmt = $mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");

To summarize:

  • All Queries should be properly parameterized (unless they have no parameters)
  • All arguments to a query should be treated as hostile as possible no matter their source

More Related Contents:

  1. Save the data into a database and return another value from database using PHP
  2. inserting information and image in php and mysql
  3. SQL injection that gets around mysql_real_escape_string()
  4. Can I mix MySQL APIs in PHP?
  5. mysqli or die, does it have to die?
  6. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  7. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  8. Reference: What is a perfect code sample using the MySQL extension? [closed]
  9. MySQL vs MySQLi when using PHP [closed]
  10. How to get count of rows in MySQL table using PHP?
  11. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  12. Why is mysqli giving a “Commands out of sync” error?
  13. How to call a MySQL stored procedure from within PHP code?
  14. MySQLi count(*) always returns 1
  15. Warning: mysqli_connect(): (HY000/2002): No such file or directory
  16. How to best store user information and user login and password
  17. Using Mysqli bind_param with date and time columns?
  18. PHP PDO and MySQLi [duplicate]
  19. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  20. Can I blindly replace all mysql_ functions with mysqli_?
  21. php password_verify() hash and pass won’t match
  22. Get number of rows matched by UPDATE query with PHP mysqli
  23. How to use mysqli_query() in PHP?
  24. PHP to MySQL SSL Connections
  25. Which is fastest in PHP- MySQL or MySQLi?
  26. Best way to connect to MySQL with PHP securely [duplicate]
  27. mysqli persistent connection
  28. Create mysql trigger via PHP?
  29. Cannot figure out how to run a mysqli_multi_query and use the results from the last query
  30. How to make mysqli connect function?

Leave a Comment