Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysqli prepared statements and mysqli_real_escape_string

I'm currently using the mysqli php extension.

Traditionally I have used mysqli_real_escape_string to escape user input. However I am looking at changing over the code (hopefully in as few steps as possible) to use prepared statements.

I want to be clear on this - provided I use prepared statements to bind all of my variables, can I be confident that sql injection is impossible? (And dispense completely with mysqli_real_escape_string?)

Thanks

like image 342
Travis Avatar asked Jun 23 '10 11:06

Travis


People also ask

What is a Mysqli prepared statements?

A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency. Prepared statements basically work like this: Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?").

What is the use of mysqli_real_escape_string () function?

The mysqli_real_escape_string() function is used to escape characters in a string, making it legal to use in an SQL statement.

Does mysqli_real_escape_string prevent SQL injection?

PHP provides mysql_real_escape_string() to escape special characters in a string before sending a query to MySQL. This function was adopted by many to escape single quotes in strings and by the same occasion prevent SQL injection attacks. However, it can create serious security flaws when it is not used correctly.

Why does mysql_real_escape_string need a connection?

mysql_real_escape_string() and prepared statements need a connection to the database so that they can escape the string using the appropriate character set - otherwise SQL injection attacks are still possible using multi-byte characters.


2 Answers

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

like image 124
Mark Byers Avatar answered Sep 19 '22 23:09

Mark Byers


Speaking of security, there is no difference between both methods, if you correctly bind or format your variables.

Binding is just simpler, because it can be used just for any case, while escaping can't (so, you have to cast some variables instead of escaping/quoting).

Also, bear in mind that no binding nor escaping can make identifier safe. So, if you have to use a field name or operator in your query, you have to use a value, hardcoded in your script.

like image 21
Your Common Sense Avatar answered Sep 18 '22 23:09

Your Common Sense