Am I safe from MySQL injection?

Question

Is it good enough to avoid SQL injection?

mysql_real_escape_string(htmlentities (urlencode($_POST['postmessage']))); 

Answer 1

I think you are confusing two security issues: SQL injection and crossite scripting (XSS) .

A website is vulnerable to SQL injection when improperly processed user input is used in an SQL query that is sent to an SQL database. This code, for example, introduces an SQL injection vulnerability:

 mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . $_POST['postmessage'] . "')"); 

This problem can be easily mysql_real_escape_string if you canceled user input using a function like mysql_real_escape_string :

 mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . mysql_real_escape_string($_POST['postmessage']) . "')"); 

This is all you need to do, but the hard part remembers this for every part of user input that is used in the SQL statement.

A website is vulnerable to cross-site scripting when user input is used in HTML that is sent to the client. This code, for example, represents an XSS vulnerability:

 echo "<div class='postmessage'>" . $_POST['postmessage'] . "</div>"; 

The XSS vulnerability was fixed by escaping user input using the htmlspecialchars function:

 echo "<div class='postmessage'>" . htmlspecialchars($_POST['postmessage']) . "</div>"; 

Again, this is easy to do, but easy to forget.

Usually, user input that is placed in the database, which will be used when sending the HTML code later, is saved unchanged. That is, only mysql_real_escape_string is used. However, you can avoid user input to prevent XSS, and then exit the XSS-safe string to prevent SQL injection:

 mysql_query("INSERT INTO postmessages (postmessage) VALUES ('" . mysql_real_escape_string(htmlspecialchars($_POST['postmessage'])) . "')"); 

The advantage is that you don’t have to remember to avoid values ​​from the database using htmlspecialchars before writing them to HTML. The disadvantage is that some values ​​can be escaped with various functions. For example, the username is likely to be escaped using htmlspecialchars , but "postmessage" may allow BBcode, Markdown, or a subset of HTML. If you avoid all the input to prevent XSS, then you will need unescape values ​​from the database, for example, htmlspecialchars_decode .

One problem is that an unescaping escape line does not always return the original line ( unescape(escape($orig)) does not necessarily match $orig ). Even when using htmlspecialchars and htmlspecialchars_decode when using a different quote style, this will cause this problem. Another example is that if strip_tags used, the information is deleted permanently; you cannot undo strip_tags . Thus, many developers prefer to use mysql_real_escape_string only to store the values ​​in the database and htmlspecialchars (or any other) to prepare the row from the database that will be used in HTML.

Answer 2

mysql_real_escape_string() is the only method you need here.

You should not do htmlentities() or urlencode() before inserting data into your database. These methods are typically code that is executed during the rendering of the view that you offer your users.

The best way to avoid SQL injection is to use prepared statements .

---

Resources:

• php.net - Prepared statements with PDO

On the same topic:

• PHP: Is mysql_real_escape_string enough to clear user input?

Answer 3

You should also make sure to use " around the place where you embed your code.

For example, if you do

 $_POST['userid'] = mysql_real_escape_string($_POST['userid']); mysql_query('SELECT * FROM user WHERE userid = '. $_POST['userid']); 

mysql_real_escape_string will not help anything. This is because $ _POST ['userid'] is not surrounded by '.

So what you have to do

 $_POST['userid'] = mysql_real_escape_string($_POST['userid']); mysql_query('SELET * FROM user WHERE userid = \''. $_POST['userid'] .'\''); 

instead.

Thus, using mysql_real_escape_string for your variables does not automatically mean that they are safe in any query.

Another approach is to use prepared statements .

Answer 4

Yes, but there is a reason not to use mysql_real_escape_string (). The first is pain type. Secondly, you must remember to use it every time. Thirdly, it makes your code ugly. Fourth, you must remember to quote your lines. Fifth, it is more difficult to insert drops in db in this way.

PDO training will improve your life in the long run. Learning harder than just using mysql_real_escape_string (), but the long-term benefits outweigh the inconvenience of the learning curve.