When you're writing SQL – for anything that takes human input really, a lot of things have been done to avoid the injection.

Everyone that's heard of SQL injection knows that (I'm going to use PHP as a sample) doing something like this isn't safe:

$sql = "SELECT * FROM `users` 
.  WHERE `userName` = "{$_POST["username"]}" 
.  AND `pass` = "{$_POST["pass"]}";";

Why can't we escape all user input using “magic”?

Magic quotes was a controversial feature of the PHP scripting language, wherein strings are automatically escaped. Special characters are prefixed with a backslash before being passed on.

The idea of using “magic escape quotes” to deal with program input that isn't sanitized correctly does not really solve the issue with SQL injection for the following reasons.

• At the time the magic is applied, it is unknown where the data will end up. So magic quotes are destroying data, unless it is written unescaped to a database.

• Not all data that are supplied by the user are intended for insertion into a database. They may be rendered directly to the screen, stored in a session, or previewed before saving. This can result in backslashes being added where they are not wanted and being shown to the end user.

• It may just be used in the HTML response sent back to the client. Think of a form that has not been filled in completely and is therefore shown again to the user. With magic quotes, the data entered on the first attempt will now be SQL escaped, which is meaningless on an HTML page. Even worse: on the second submission the data is SQL escaped again.

• Adding magic quotes and subsequently removing them where appropriate incurs a small but unnecessary amount of performance overhead.

• Magic quotes also use the generic functionality provided by PHP's addslashes() function, which is not Unicode-aware and is still subject to SQL injection vulnerabilities in some multi-byte character encodings.

• Magic quotes do not protect against other common security vulnerabilities such as cross-site scripting attacks or SMTP header injection attacks.

The addslashes() command escapes all the quotes which prevents hackers using something like:

' OR 1=1

addslashes does NOT make your input safe for use in a database query! It only escapes according to what PHP defines, not what your database driver defines. Any use of this function to escape strings for use in a database is likely an error - mysql_real_escape_string, pg_escape_string, etc, should be used depending on your underlying database as each database has different escaping requirements. In particular, MySQL wants \n, \r and \x1a escaped which addslashes does NOT do. Therefore relying on addslashes is not a good idea at all and may make your code vulnerable to security risks.

Single quotes are not the only special character that can break a SQL query; however quotes are the only thing which addslashes handles.

The PHP directive magic_quotes_gpc was on by default in some versions of PHP, and it essentially automatically ran addslashes() on all GET, POST, and COOKIE data. If addslashes() was then run on the data that had already been escaped with magic_quotes_gpc it would result in double escaping. The function get_magic_quotes_gpc() could come in handy for checking this.

Even the documentation for addslashes says that you shouldn't use addslashes, but instead to use database-specific functions such as mysql_real_escape_string().

addslashes() also has issues with multibyte characters:

When using addslashes() on a string that includes cyrillic characters, addslashes() totally mixes up the string, rendering it unusable.

In addition,

' 27
\ 5c
뼧 bf 5c

Those are two bytes, but only one Unicode character.

Since addslashes does not know anything about Unicode, it converts the input bf 27 to bf 5c 27. If this is read by a Unicode-aware program, it is seen as 뼧'. Boom.

There is a good explanation of this issue at http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string.

Demonstrating an SQL injection attack that is immune to addslashes() is a bit more involved, but still pretty straightforward.

In GBK, 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).

How does this help? If I want to attempt a SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is interpreted as a single character, not two. Oops, there goes the backslash.

For convenience, single-byte representations are in parentheses. Keep in mind that this is not necessarily how these bytes are interpreted.

0xbf (¿) is used in this example, because the attack is assuming:

1. Single quotes are being escaped with a backslash.

2. 0xbf5c27 (¿\) is a valid multi-byte character in whatever character encoding the database is using.

In other words, 0xbf (¿) isn't a magic byte that works in all cases. It's a specific one used in this particular attack, because when followed by 0x5c (\), it makes a valid multi-byte character, 0xbf5c27 (¿\).

Because 0xbf27 (¿') is not a valid multi-byte character, this particular example doesn't work if single quotes are being escaped with single quotes. Therefore, instead of 0xbf(¿), the attack would use a byte (or bytes) that can be followed by 0x27 (') to form a valid multi-byte character.

It's the exact same attack. Using single quotes to escape single quotes offers no protection. Again, a single quote isn't always a single quote, and that's really the focus of this particular post.

They are a little better because they ensure that the escaping interprets the data in the same way the database does.

From a security point of view, they are okay if you use them for every single database input. But they have the risk that you may forget either of them somewhere, or that you use xxx_escape_strings for numbers without adding quotation marks around them in the SQL statement and without ensuring that they are actual numbers by casting or converting the input to the appropriate data type.

From a software development perspective they are bad because they make it a lot harder to add support for other SQL database server software.

DBMSes have different escape specification for identifiers (e.g. Table name, field name) than parameters. Not all DBMS provide escape API.

All escaping a string does is suitably apply slashes before a quotation mark, but it doesn't prevent keywords being used in the string that is applied to the SQL, for example having multiple quotes can confuse the real escape string (in combination with incorrect character encoding such as

'\\''' DELETE FROM table'

now, the real escape strings might escape the quotes but the string will still contain the DELETE clause.

Please read https://phpdelusions.net/sql_injection and http://stackoverflow.com/a/12118602/3536236.

Escaping a string might also cause issues with the name John O'Groats.

INSERT INTO users SET name='John O'Groats';

This statement will cause an error, which could be used to gain information from the system.

WARNING: Escaping is inadequate to prevent SQL injection, use prepared statements instead!

See: https://paragonie.com/blog/2015/05/preventing-sql-injection-in-php-applications-easy-and-definitive-guide

Some times escaping may work, but it's not consistent enough to warrent using.

Some may think it's sufficient to escape single quotes with single quotes, ie. '' (2 single quotes) instead of \'.

For example,

$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['username']);
 
$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\\\''), $_POST['password']);

will make the injection attempt successful, whereas

$mysql['username'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['username']);
 
$mysql['password'] = str_replace(array('\\', '\''), array('\\\\', '\'\''), $_POST['password']);

will make the injection attempt fail.

The latter method (str_replace(array('\\', '\), array('\\\\', '\'\), $data)) may sometimes be sufficient for preparing data for a mysql query where apostrophe is the enclosure character?

The following examples are assumed safe - but again, it's far better to use prepared statements:

mysql_set_charset($charset);
mysql_query("SET SQL_MODE=''");
$var = mysql_real_escape_string('" OR 1=1 /*');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

…because we've explicitly selected an SQL mode that doesn't include NO_BACKSLASH_ESCAPES.

mysql_set_charset($charset);
$var = mysql_real_escape_string("' OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

…because we're quoting our string literal with single-quotes.

$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(["' OR 1=1 /*"]);

…because PDO prepared statements are immune from this vulnerability (provided either that you're using PHP≥5.3.6 and the character set has been correctly set in the DSN; or that prepared statement emulation has been disabled).

$var  = $pdo->quote("' OR 1=1 /*");
$stmt = $pdo->query("SELECT * FROM test WHERE name = $var LIMIT 1");

…because PDO's quote() function not only escapes the literal, but also quotes it (in single-quote ' characters); note that to avoid a Multi-Byte bug in this case, you must be using PHP≥5.3.6 and have correctly set the character set in the DSN.

$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "' OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();

…because MySQLi prepared statements are safe.

The following should not be used:

• addslashes
• magic_quotes
• DBMS specific _real_escape_string

Remember to slash underscores (_) and percent signs (%), too, if you're going use the LIKE operator on the variable or you'll get some unexpected results.

http://www.sqlserverlogexplorer.com/prevent-sql-injection-in-sql-server/