Short answer
It is right.
Long answer
This is correct, but your question indicates a lack of understanding of what is happening here. Here is your request:
INSERT INTO table (column, column2) VALUES ('{$escaped_value}', "0")
Let's see what happens if you do not leave, and the user enters the following data:
Eve'
Remember that you just pass the string to MySQL, the insert is done by processing the PHP string. This means that in this case, the query sent by mysql_query is:
INSERT INTO table (column, column2) VALUES ('Eve'', "0")
Which is a syntax error and will result in a page error. Now that a user like me (i.e., the Bastard seeking to make your day unhappy: D) notices one of them, we know it's time to play. What happens if the user provides the following data?
Eve', "0"); DROP TABLE table
Our request has been expanded to:
INSERT INTO table (column, column2) VALUES ('Eve', "0"); DROP TABLE table
This is not a syntax error, but it is problematic ... now we are executing a query that we never expected! (If you do not recognize it, "-" means a comment in SQL, that is, "ignore everything after this point").
Now this cannot happen in this particular case ( mysql_query does not support multiple queries ), but this is the attack you are trying to prevent - an attack class known as SQL injection . Let's see what happens when you use mysql_real_escape_string!
The entered data becomes:
Eve\', \"0\"); DROP TABLE table
This means our query string looks like this:
INSERT INTO table (column, column2) VALUES ('Eve\', \"0\"); DROP TABLE table--}', "0")
What well! Data entered by the user will be stored in the database as it is entered. Not without quotes and not with additional backslashes, but the way they entered. This is important. If you store data in any other way, later you will have problems with unshielding or double shielding. In addition, the addition of additional slashes or the like often leads users to be open to users anyway, and this is a gigantic warning sign that things cannot be properly shielded. You want to avoid the wrong actions and especially want to avoid the wrong actions and advertising about it, which leads me to the following section:
Alternatives to shielding your data
- Magical quotes. As you noticed, it is not recommended (and not without reason) to avoid.
- Do not avoid your data. I would advise against this option.
- Remove bad characters from input. Annoying in almost all situations, you should store what users enter and not what is technically easy to store.
- Prevent bad characters from entering. Sometimes acceptable (credit card number fields should not handle quotes), sometimes annoying, sometimes a massive warning sign (for example, in a password field)
Prepared statements. I said that the "filling" of the variables in the string was done by PHP, and MySQL just got the query string, hence the need for escaping. Prepared statements will offload this work, being a little smarter, and using prepared statements will look something like this (warning: pseudocode):
$ Statement = $ db-> prepare ('INSERT INTO table (column, column2) VALUES ("% 1", "% 2")'); $ result = $ statement-> execute ($ value1, $ value2);
There is a good question about the stack overflow about SQL escaping methods, and the answers to them go deeper, so you can read about it.
Personally, I like this option. You cannot forget to shield the variable and insert it into the database in this way - either the values are correctly distributed, or they are not near the database, there is no halfway option. That is, if you undertake that all requests go through prepared statements, rather than string concatenation, as
$db->prepare('INSERT INTO table VALUES('.$value.')'))
Done. In my opinion, this is easier than tracking which variables are cleared and which are not. Some people like to avoid strings as soon as they come from the user, but it is very embarrassing if they can go anywhere other than the database - back to HTML, to memcache, etc. If you are going to do it yourself, I could suggest some Hungarian notation, for example:
$uValue = $_POST['value']; $sValue = escape($uValue); $db->query('INSERT INTO table VALUES(' . $sValue .')');
I first saw this idea in an excellent article by Joel Spolsky: " Wrong code looks wrong ."
Conclusion
Hope you feel better prepared for building injection sites now. Good luck with any method you choose and have fun by always avoiding user input before it enters the database! ;)