Why is MySQL more susceptible to SQL Injection attacks?
I have been searching for a perfect answer to this question for the last two days but in vain. It would be better if you could compare MySQL to other databases in terms of the SQL Injection vulnerability and security.
PS: I am well aware of MySQL and SQL Injection attacks. Please try to understand what the question is all about.
Why is MySQL more susceptible to SQL Injection attacks
Mysql is not. And never have been.
MySQL is no more susceptible than any other DBMS. It's not the wand, it's the wizard, you know. It is not mysql, but its users who are more susceptible to SQL Injection attacks.
That's all.
And the problem with users still persists, as it is vividly demonstrated by the other answer. Follow its recommendations (extremely common among php/mysql folks, mind you) and in no time you'll enjoy a first class SQL injection.
What is SQL Injection
SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.
SQL Injection Example
Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.
MySql and Code
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
// our MySQL query builder really should check for injection
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
// the new evil injection query would include a DELETE statement
echo "Injection: " . $query_evil;
Display
SELECT * FROM customers WHERE username = ' '; DELETE FROM customers WHERE 1 or username = ' '
If you were run this query, then the injected DELETE statement would completely empty your "customers" table. Now that you know this is a problem, how can you prevent it?
Injection Prevention - mysql_real_escape_string()
Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.
What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.
Lets try out this function on our two previous injection attacks and see how it works.
MySQL & PHP Code
//NOTE: you must be connected to the database to use this function!
// connect to MySQL
$name_bad = "' OR 1'";
$name_bad = mysql_real_escape_string($name_bad);
$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection: <br />" . $query_bad . "<br />";
$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";
$name_evil = mysql_real_escape_string($name_evil);
$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection: <br />" . $query_evil;
Display
Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''
Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous: •Bad: \' OR 1\' •Evil: \'; DELETE FROM customers WHERE 1 or username = \'
And I don't think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!
