PHP SQL Injection

I've read in several places that htmlspecialchars is not enough to prevent SQL injection attacks. I'm working with a legacy codebase and it uses this to sanitize user input:

stripslashes(htmlspecialchars(trim($value), ENT_QUOTES, 'UTF-8'))

My gut tells me that this is also unsafe but my coworker insists that it is. I don't have much experience in working with plain PHP so coul开发者_开发问答d someone please tell me why this is unsafe so that I can convince my coworker to use something better?

I've read in several places that htmlspecialchars is not enough to prevent SQL injection attacks

It protects against XSS attacks, but SQL is not HTML so it does nothing for SQL injection.

(You should move the htmlspecialchars encoding to "before inserting into HTML" instead of "before inserting into SQL")

My gut tells me that this is also unsafe but my coworker insists that it is.

Your gut is right. The fact it leaves quote characters alone shouts unsafe!.

Take a look at Bobby Tables. It demonstrates the problem and provides a number of solutions. Anything that uses bound parameters is good.

Use prepared statements.

disable magic quote in php.ini and use PDO. bum

htmlspecialchars to escape params in SQL is the ugliest

It may prevent you from XSS, but not from SQLi, because it doesn't quote any SQL-specific (or DBMS-specific) special characters. The most modern solution is to use PDO with Prepared Statement or PDO:quote(). Legacy solutions cover mysql_escape_string() and such. Refer the manual about the db-driver you are using, about the features it provides to prevent you from SQLi.

You should be calling a database specific escaping function on things you insert into queries. For a MYSQL database, use mysql_real_escape_string.

It depends on the type of SQL query it is injecting. SQL injections in string fields (enclosed with ' and ") can be disabled by encoding or removing this characters. But in general this is not the solution!

You should NEVER EVER concatenate the SQL string together and send it to the database, especially if it contains user supplied data. You should always use the prepare statement to prepare a SQL statement with placeholders and then pass the parameters separately. Yes, this means that you will probably need to have more than one line of code and you will call corresponding SQL functions.

This is the only good solution for this that is implemented in all programming languages.

mysql_real_escape_string would be better than mysql_escape_string as it has been deprecated.