Cleaning string before entering to mysql PHP

Question

As I can tell, all the mysql (not using the the MySQLi extension) functions are not considerable anymore, they're basically deprecated. Therefore, I'm using the MySQLi extension now. And for a new script I'm coding, I want to know if I should escape strings before using the prepared statements. If you should escape, then what is the best way to do so?

My current function is the following, but I don't believe it is valid, or even necessary.

function clean($string){
    return mysql_real_escape_string($string);
}

MySQL functions have been superseded with the MySQLi functions (and a bunch of other database accessing functions). `mysql_real_escape_string` still works, it's "just" that it's in a deprecated package. — zneak, Apr 03 '13 at 17:51

Your Common Sense · Answer 1 · 2013-04-03T13:29:22.900

If there is a better way to do it please tell me how,thanks.

yes

function format_mysql_string($string){
  return "'".mysql_real_escape_string($string)."'";
}

it doesn't have word "clean" in it's name because mysql_real_escape_string doesn't "clean" anything.
it have words "format" and "string" in it's name because this function have to be used to format SQL strings, and nothing else.
it does add quotes because string formatting require quotes. Therefore, your query will fail if you try to format anything else - so, you will know that you need another measure.

score 1 · Accepted Answer · answered Apr 03 '13 at 20:01

Okay, so you've already found out that you shouldn't be using the mysql_xxx() functions. That's good.

The alternatives are the mysqli and PDO libraries.

Both of these libraries do offer equivalent functions to mysql_real_escape_string, but they also offer a better alternative, called Parameterised Queries (or Prepared Statements).

This is an alternative method of writing queries with variables which is considered much better practice than using the escape string method.

What happens is that you define your query with placeholders rather than variables; something like this:

SELECT * FROM table WHERE id = :id

where :id is the placeholder.

You call a function with the query as above with the placeholders, and then separate calls to associate your variables with each of the placeholders.

The exact code for this differs between the mysqli and PDO libraries, but in both cases the variables are sent to the DB server separately from the query, and thus there is no chance of a SQL injection attack.

The PDO library is generally considered the better option. Here are some links to good sites with examples of how to write the code:

Hope that helps.

Cleaning string before entering to mysql PHP

2 Answers2