Every time, I insert text with quotes into my MySQL database, a “” is placed before each quote. And each time that entry is edited, the number of “” doubles, because not only does it need to escape quotes, but also the existing “” too.
When updating entries with PHP, is there a way to not have like 500 “” inserted? I only need the bare minimum of escaping to protect against MySQL injection attacks, but 500 pairs of them is just overkill.
Check if PHP’s magic_quotes directive is activated. That’s probably messing with your incoming $_POST and/or $_GET data. Check with your host to see if you can deactivate it, and if not you can still manually apply stripslashes to $_POST, $_GET and $_COOKIE at the beginning of your script to cancel it out.
If I turn off magic_quotes, will my database be susceptible to SQL injection attacks?
No, like voetsjoeba said you still need to manually do safe quotes and such on post/get/cookie but magic_quotes is a really weird functionality that came during an era of a lot of security problems. In general it will cause a decent to great developer a lot of headache but it might save a bad one.
The thing about magic_quotes is that it was originally introduced as a means of automatically preventing SQL injections (mostly) for novice PHP users who had no idea of the security implications of directly injecting user input into queries. They would do things like
mysql_query("DELETE FROM table WHERE name = '".$_GET['name']."'");
and not realize what’s wrong with that (and sadly to this day I still see this happen surprisingly much). This is part of the reason why PHP used to have the reputation of being “insecure”, when really the only reason was the people using PHP failing to understand the need for proper user input filtering.
Here’s where magic_quotes comes in to provide a failsafe by automatically applying addslashes to all get/post/cookie data before even making them available to the script. That way, when the $_GET/$_POST data gets inserted into the query as above, it’s already escaped. Unfortunately, for people who actually know what they’re doing and knowingly apply mysql_real_escape_string to all user-input data before inserting them into their queries, this causes the data to be escaped twice and thus wrong values to be entered into the database.
Because magic_quotes is applied before the actual script begins executing, there’s no way of turning it off from inside the script itself. A standard solution is to recursively apply [URL=“http://www.php.net/manual/en/function.stripslashes.php”]stripslashes to all $_GET, $_POST and $_COOKIE data on every page load before the rest of your script is executed, of course wrapped inside an if(get_magic_quotes_gpc()) to make sure you’re not overzealously stripping backslashes.
Bottom line: make sure to always apply mysql_real_escape_string (do not use addslashes instead!) to user-input data before inserting them into queries, and you’ll be safe from SQL injections. When dealing with backslash hell it’s often easy to lose track of things, so if you find yourself lost just remember that applying mysql_real_escape_string to data before entering it into queries will ensure that the data is entered into the database exactly as it was fed to mysql_real_escape_string. Think of mysql_real_escape_string as a bodyguard that ensures that the data you feed it is transferred to the database safely and exactly as you handed it. What I’m saying is: never doubt mysql_real_escape_string, and never doubt the need for it.
wise man once told me, magic_quotes is the devil, mysql_real_escape_string is the way to go. I wonder who told me that [whisper]voets[/whisper]
So I read about magic quotes. It seems to do the same thing as mysql_real_escape_string, except it does it to EVERYTHING, not only the things you’re going to put in some SQL.
Was my problem using mysql_real_escape_string with magic quotes turned on? That would surely do double damage. What if I just leave magic quotes on and not use mysql_real_escape_string? Will I still get this 500 “” thing?
You’re right about magic_quotes adding slashes to everything, but not about them doing the same thing. They might seem similar, but you must always use mysql_real_escape_string when entering user-input data into queries. Probably the biggest reason why is that addslashes is completely oblivious of any character set other than ASCII. Under certain circumstances, this can enable attackers to bypass the effect of addslashes, which you don’t want. In contrast, mysql_real_escape_string takes the current character set used by the connection to MySQL into account. Another reason is that addslashes does not escape everything mysql_real_escape_string does.
That’s probably what happened, yes.
If the reason is indeed having both magic_quotes and mysql_real_escape_string applied, then removing either one should take care of the extra backslashes being generated. However, you really should get rid of magic_quotes and stick with mysql_real_escape_string for reasons explained above.
I understand fully. Thanks for the great explanation.
:: Copyright KIRUPA 2024 //--