Avoiding SQL Injection

Is this function enough?

[asantos]

I use this function everytime im going to save any data in a sql sentence:

function q($s,$string=false) {
$s = mysql_real_escape_string($s);

# Adds quotes if is string
if ($string) { $s = "'".$s."'"; }

return $s;
}

Example of use:
$sql = 'SELECT * FROM tableXYZ WHERE id='.q($id);

Or:
$sql = 'SELECT * FROM tableXYZ WHERE title='.q($title,true);

Do you think is enough? (it must work in environments with magic_quotes on¦off)

[dreamcatcher]

Hi asantos,

According to the PHP manual:

Using mysql_real_escape_string() around each variable prevents SQL Injection

[uk2.php.net...]

dc

[asantos]

hi dreamcatcher, thanks for the msg. you're right, but in a specific case it adds more slashes. example:

$var contents: my name is "andres"
applying mysql_real_escape_string() to the $var returns:

magic_quotes_gpc OFF
my name is \"andres\"

magic_quotes_gpc ON
my name is \\\"andres\\\"

Of course i could use something like this:

if(!get_magic_quotes_gpc()) {
return mysql_real_escape_string($value);
}

But WHAT would happen if get_magic_quotes_gpc() is ON, would my function still be Sql Injection Attack FREE?

[whoisgregg]

Why use Magic Quotes [us3.php.net] vs. Why not to use Magic Quotes [us3.php.net]

Moral of the story? Disable magic quotes. (PHP 6 has actually removed it entirely.) Write code to detect when it's on and run stripslashes on everything, then run the correct type of escaping function on data that needs to be escaped.

Good info in the comments on the main magic quotes page [php.net] too.

[asantos]

whoisgregg,

the hosting service doesn't allow me to edit the php.ini so my best guess to deactivate the magic_quotes is to do it through the .htaccess like this:

php_flag magic_quotes_gpc off

Should i use another flag or that line completely deactivates magic quotes?

[FourDegreez]

The way I do it is I always call this function first on all values passed in POST or GET:

function fixInput($value) {
if (get_magic_quotes_gpc())
return stripslashes($value);
else
return $value;
}

Then when I am going to put something into a SQL statement, I call this function for strings:

function escapeField($value, $emptyToNull = FALSE) {
if ($emptyToNull && (!isset($value) ¦¦ strlen($value) == 0))
return 'NULL';
else
return '\''. htmlentities(mysql_real_escape_string($value), ENT_NOQUOTES) .'\'';
}

The use of htmlentities is to protect from HTML injection (cross-site scripting or whatever they're calling it) when retrieving the data later. Of course I could've applied that function after every read, too.

Anyway.. if the value going into the SQL is (or should be) a number, I call intval() on it.

[dreamcatcher]

hi dreamcatcher, thanks for the msg. you're right, but in a specific case it adds more slashes.

That is correct. Thats why you should always follow FourDegreez's advice and check first.

dc

[asantos]

Thanks for the function. Ill make some tests and come back if i get any problems.

[asantos]

Mmm, just one more question:

If i use this in the .htaccess:
php_flag magic_quotes_gpc off

It will deactive
magic_quotes_gpc

But not:
magic_quotes_runtime ... what is magic_quotes_runtime for?

[coopster]

There is an Appendix [php.net] in the PHP online manual that describes all the configuration directives and how they can be edited. It is quite handy.

If magic_quotes_runtime [php.net] is enabled then any data coming from external sources such as database result sets and text files will have quotes escaped.

[asantos]

Thanks coopster.
Im using this on my .htaccess file now:

php_flag magic_quotes_gpc off
php_flag magic_quotes_runtime off

[coopster]

I run with both off as well. If you can change it in the

php.ini

, even better.

[asantos]

What would be the main difference between changing those options from the php.ini OR .htaccess?

[coopster]

Speed. Anytime you can avoid using .htaccess files you should do so. They are per-directory override files that are looked at each and every request to the directory structure in which they reside. If you have control over your httpd.conf and php.ini files, make your configuration changes there. If not, such as on a shared server, then you don't have much choice but to employ per-directory overrides (.htaccess).

.htaccess files [httpd.apache.org]

[asantos]

Thanks for the explanation.

[asantos]

Modifying FourDegreez's code i came up to this solution (for own needs):

//For SQL queries
//***************
function q($data,$string=false) {
 $data = stripslashes($data);
 $data = htmlentities($data,ENT_QUOTES);
 $data = mysql_real_escape_string($data);
 if ($string) { $data = "'".$data."'"; }
 return $data;
}

//Used when SAVING data
//*********************
function s($data,$max,$html=false) {
 $data = substr($data,0,$max);
 if($html) {
 # Allow certain tags only
 $data = strip_tags($data,'<p>,<br>,<img>,<a>,<strong>,<em>,<blockquote>,<ol>,<ul>,<li>,<span>');
 } else {
 $data = strip_tags($data);
 }
 return q($data);
}

When you want to save a 'title' that has max 100 chars long:
s($title,100);

When you want to save a 'html block' that has max 65535 chars long:
s($html_block,65535,true);

If someone sees an error, please let me know. Thanks. Andres.