secure mysql usage

[Dennis_Gull]

Hello guys,
I have a couple of quick questions regarding mysql queries.
Right know I check the database string values that people insert from the site like this:

if (get_magic_quotes_gpc()) {
return stripslashes($input);
} else {
return mysqli_real_escape_string($db,$input);
}
This is just a guess but won't I need to add mysqli_real_escape_string after stripslashes if I got magic quotes on and will mysqli_real_escape_string be enough to secure the string?

And will I also have to check the inputs with mysqli_real_escape_string once people want to load data (besides for inserting data) and is there a way I can check if the data is a number instead of string? :confused:

thanks in advance

[Nile]

You should be using mysqli_real_escape_string, and htmlentities just to be safe.

Here's to check if it's an integer:


<?php
if(!is_int($string)){
echo "This is the output if \$string is not a number.";
} else {
echo "This is the output if \$string is a number.";
}
?>


To see if it is numeric, change the is_int to is_numeric.

[Twey]

Learn to use PDO (http://www.php.net/PDO). Prepared statements and bound variables are a blessing — security is pretty much taken care of automatically.

You're quite right, stripping the slashes makes it, if anything, more important to escape the values. The code should look like this:
return mysqli_real_escape_string(get_magic_quotes_gpc() ? stripslashes($input) : $input);A neater solution, though, might be to use a wrapper function around GET/POST/COOKIE values in the first place:
function g($k) {
return get_magic_quotes_gpc() ? stripslashes($_GET[$k]) : $_GET[$k];
}Alternatively, simply disable magic quotes (http://www.php.net/manual/en/security.magicquotes.disabling.php) in the first place.

[Dennis_Gull]

Thanks for the info guys, this is really useful! :)

[Nile]

Glad to help you Dennis.

[Dennis_Gull]

just a little side question, when I use htmlentities to clean out the html tags and special characters and then load it with ajax I will see the cleaned code instead of the "converted", example:

I insert "hello & welcome" and use htmlentities on it, I will then output:
"hello &amp; welcome" once I load it (with ajax that is).. is there a way to bypass this?

[Nile]

PHP: Str_replace() (http://us2.php.net/str_replace)

[Twey]

If you're seeing those then you don't want to be using htmlentities(). htmlentities() is only for data that's going to be inserted into an HTML page; if it isn't going to be inserted then you don't need to do it. Additionally, ensure that the operation is only performed once on any input.

[Dennis_Gull]

Do I have to replace all the special characters again? :confused:
Won't that break the xml structure again? :) I could of course replace them inside javascript if thats necessary, was just hoping for a simple feature to do this backwards.

[Nile]

I think you would do something like this:


<?php
$content = "A &amp; B";
$content = str_replace('&amp;','&',$content);
echo $content;
?>

[Twey]

No, you'd just leave it alone. If you're seeing &amp; on the actual page then it's already been escaped and you're doing it twice. Doing it again would yield &amp;amp;, &c.

[Nile]

Yes... But if he wants &amp; to display... He should replace it with a '&' like my code does..

[Twey]

If that were the entire description of the problem rather than just one example, then yes, you would be correct. However, as it stands, it sounds as if Dennis_Gull has merely misapplied htmlentities(). str_replace()ing each entity with its corresponding character is huge and slow, and won't help much.

[Dennis_Gull]

The problem is that php / html will display & instead of &amp; which is good because I want the code to be clean but when I use ajax (javascript, php and xml) to get the data It will display &amp; instead of & so I will probably have to use str_replace when I want to use ajax.

[Twey]

If you're using XML then your XML parser on the receiving end should interpret the characters correctly.