Is htmlspecialchars() and mysql_real_escape_string() enough?

[johnsmith153]

In MySQL and when relying on a professional web host to manage the server (including the database server) I want to ensure my PHP code reduces the risk of hackers accessing my database (SQL Injections etc.). Is htmlspecialchars() and mysql_real_escape_string() enough, or are there additional requirements?

 

Of course a programmer needs to know what they are doing for other parts of the code, but my question is simply to check if these commands are enough on their own when adding / retrieving data from a database - or do you need some form of manual way to check for other types of input.

[PFMaBiSmAd]

mysql_real_escape_string() is enough to prevent sql injection in string data (i.e. data that is put between single-quotes in a query), because escaping the data will prevent a hacker from breaking out of the single-quotes.

 

However, this does nothing for numerical data that would normally be put into a query without any single quotes around it (putting single quotes around it is an option, but at least mysql converts this data to floating point which has its own issues.) You need to either validate numerical data or cast it as a numerical data type in order to prevent sql injection. The reason for this is because it is possible to use hex encoded data (which is automatically treated as a string) that contains no quotes (mysql_real_escape_string() has no affect on it) to inject sql.

 

htmlspecialchars() has nothing to do with sql injection because it only operates on HTML special characters, which don't have anything to do with sql or sql injection. HTML special characters in your data does however have significance in javascript and html being injected into content that you display on your web page. You would typically use htmlentities() (not just htmlspecialchars()) on any content that you output on a web page that originated as input from a visitor.

 

 

[johnsmith153]

Someone once told me to do this on the code (before any interaction with the database):

 

$value = str_replace("_", "^1", $value);
$value = str_replace("#", "^2", $value);
$value = str_replace("-", "^3", $value);
$value = str_replace("&", "^4", $value);
$value = str_replace("=", "^5", $value);
$value = str_replace(":", "^6", $value);
$value = str_replace("+", "^7", $value);
$value = str_replace("/", "^8", $value);
$value = str_replace("%", "^9", $value);
$value = str_replace("\x00", "^0", $value);
$value = str_replace("\n", "^A", $value);
$value = str_replace("\r", "^B", $value);
$value = str_replace("'", "^C", $value);
$value = str_replace("\"", "^D", $value);
$value = str_replace("\x1a", "^E", $value);
$value = str_replace("\\", "^F", $value);
$value = str_replace("**", "^G", $value);

 

..and then replace it all agan before displaying to the user.

 

What do you think to all that?

[PFMaBiSmAd]

It's a waste of time. None of that would prevent sql injection through a numeric value.

 

As long as you escape string data and validate/cast numeric data, you don't need to do anything else to prevent sql injection.