Should I use htmlspecialchars or mysql_real_escape_string or both [duplicate]

Question

I have let the user enter some information (name, date of birth etc). Then I have to insert these values to the database. Should I use mysql_real_escape_string() to prevent a mysql injection and htmlspecialchars() to handle the html tags, are both of them needed or will one of them do?

If I should use just one of them, then which one? If I should use both, then which one first and which one last?

Answer 1

Should I use mysql_real_escape_string to prevent the mysql injection

No. Use prepared statements and parameterized queries. This will require you to stop using the obsolete mysql_* library in favour of something more modern (like PDO).

and htmlspecialchars to handle the html tags both or one of them can do the work?

Use htmlspecialchars to protect against XSS attacks when you insert the data into an HTML document. Databases aren't HTML documents. (You might later take the data out of the database to put it into an HTML document, that is the time to use htmlspecialchars).

Answer 2

No mysql_real_escape_string()! You should use PDO. It uses prepared statements, which will not be vulnerable to injection attacks because MySQL is given the unparameterized SQL first and then given the data to plug in.

For example:

$dbh = new PDO();
$stmt = $dbh->prepare('INSERT INTO data (something) VALUE(:userInput)');

// No mysql_real_escape_string necessary
$stmt->execute(array(
    ':userInput' => $_POST['userInput']
));

htmlspecialchars() shouldn't be used on all input, but it should be used! Although typically applied after data is retrieved from the db (although, it might be a good idea to do it before in case it is forgotten afterward), it is useful for user input that you will be echoing into your HTML pages. It protects you against XSS (Cross Site Scripting) attacks, in which a malicious user can add <script> tags that contain malicious code into an input field on your site. When other users visit the page on which this malicious user posted, their browser will interpret the evil scripting, which could do things such as steal session ids or attempt CSRF (Cross Site Request Forgery).

Bottom line: You should use it before echoing any user content to your pages. Unless that content has been validated by a rigorous filter (like one for birthdates which only accepts mm/dd/yy). If you're unsure, then use it anyways. It won't hurt. It will only help!

Further Reading:

• How can I prevent SQL injection in PHP?
• What makes an input vulnerable to XSS?