mysql_real_escape_string() when to use?

[echo_loser]

Ok, it’s pretty obvious when someone SHOULD use mysql_real_escape_string(), but other times I am confused:

 

1) When variable are $_GET [] through URL and used in a SELECT statement. I am thinking most likely YES since user can just alter the URL link anyway they wish.

 

2) When variables come from ALREADY inserted values into database BY ADMIN and is used in SELECT statement. I’m thinking it’s NOT needed here? Variables in question here are used to populate an HTML <select> box.

-----

Also, two final questions:

 

3) If I use mysql_real_escape_string() on a variable and then use it in a SELECT statement, do I have to use the mysql_real_escape_string() AGAIN on that SAME variable if I use it in an INSERT statement a few rows down? Or, do I just need to mysql_real_escape_string() it once?

 

4) I do not use the second parameter (link identifier) because I am using only one db connection link in the script. Is this ok?

$var = mysql_real_escape_string($var)

 

[requinix]

1) When variable are $_GET [] through URL and used in a SELECT statement. I am thinking most likely YES since user can just alter the URL link anyway they wish.

Yes, and for that reason.

 

2) When variables come from ALREADY inserted values into database BY ADMIN and is used in SELECT statement. I’m thinking it’s NOT needed here? Variables in question here are used to populate an HTML box.

No. The first reason is that the values might contain quotes that will mess up your SQL queries. You need to protect against that happening, whether it's accidental or not. The second reason is a blanket rule: you cannot trust anything that comes from a browser. Period. If they're an administrator it doesn't matter. If you have JavaScript validation or sanitization it doesn't matter. It's all equally untrustworthy.

 

3) If I use mysql_real_escape_string() on a variable and then use it in a SELECT statement, do I have to use the mysql_real_escape_string() AGAIN on that SAME variable if I use it in an INSERT statement a few rows down? Or, do I just need to mysql_real_escape_string() it once?

If I understand you right, only do it the one time. mysql_real_escape_string() give you an altered string that's safe for SQL queries - it doesn't do any hidden magic like mark a variable as special or whatever. If you did it a second time on the new string then you'd be doubly-escaping it.

 

4) I do not use the second parameter (link identifier) because I am using only one db connection link in the script. Is this ok?

$var = mysql_real_escape_string($var)

Yeah, that's fine. Vast majority of scripts don't need two database connections open at once so it's rarely a problem.

[echo_loser]

Deeply bows as a sign of appreciation and respect.

[echo_loser]

Using intval() on integers by the way - thanks again!

[dazman]

just use mysql_query( for all queries where you need to do not need to escape.

 

make a function like so and use it for about 98% of your queries. There may be the odd requirement where you don't want to use it, but hey thats what mysql_query( is for.

 

function mysql_escaped_query($queryToEscape)
{
           return mysql_query(mysql_real_escape_string($queryToEscape));
}

 

[Pikachu2000]

That function is the wrong way to do it. You only escape string values in a query string, not the entire query string. Additionally, mysql_real_escape_string() is for string values. Numeric values should be handled by validating them and casting them to the correct type.

 

Here is the query that function generates, note the extra slashes.

 

 

Function:

SELECT location, DATE_FORMAT( \'%m-%d-%Y\', date_entered) AS f_date FROM table WHERE field = \'blue\' OR field = \'Bob\'s or Cindy\'s house. \\o/ yay!\'

 

Correct:

SELECT location, DATE_FORMAT( '%m-%d-%Y', date_entered) AS f_date FROM table WHERE field = 'blue' OR field = 'Bob\'s or Cindy\'s house. \\o/ yay!'