避开 mysql 的 SQL 注入实际转义字符串()

Is there an SQL injection possibility even when using mysql_real_escape_string() function?

Consider this sample situation. SQL is constructed in PHP like this:

$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

I have heard numerous people say to me that a code like that is still dangerous and possible to hack even with mysql_real_escape_string() function used. But I cannot think of any possible exploit?

Classic injections like this:

aaa' OR 1=1 --

do not work.

Do you know of any possible injection that would get through the PHP code above?

转载于:https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string

weixin_41568184
叼花硬汉 They just removed that extension on PHP 7.0 and it is not 2050 yet.
3 年多之前 回复
csdnceshi56
lrony* mysqli_real_escape_string()
接近 4 年之前 回复
csdnceshi68
local-host if they really want to type it in. If it would break your app, then your app is already broken. At best, rejecting such a name for technical reasons is a band-aid; at worst, it's false security.
大约 6 年之前 回复
csdnceshi58
Didn"t forge I can't agree with you. The best way is always to check values cause is does not mean that all integers are expected, we don't want to insert a value that the user don't want too. If you are inserting data, you must : check the content you are expecting and format the value to be standardized or workable. Here, if you check it and you got a non-well formated user name, you could return "Hey your value is not valid, please fix it."
大约 6 年之前 回复
csdnceshi68
local-host With ints there might be an even better way than that: rather than checking, simply turn it into what you're expecting. $value = (int) $value; or $value = intval($value);. It handles things like negative signs, which ctype_digit won't.
大约 6 年之前 回复
csdnceshi50
三生石@ A [space] character after the two dashes ( -- ) in the last may make the query valid. aaa' OR 1=1 --[SPACE_HERE]
6 年多之前 回复
csdnceshi58
Didn"t forge There is only one ultimate way to protect you against SQL injection. Just check the variable contains what you are expecting for. If you are expecting an integer, use ctype_digit... IN most case you shoudl surround it with "" or ''. and escape in variable matching quotes...
6 年多之前 回复
csdnceshi66
必承其重 | 欲带皇冠 It will be removed eventually. Servers don't really do automatic updates as you claimed. Most servers are running LTS versions of Linux so they are still running relatively old PHP versions (lots of servers still on PHP 5.1 or 5.2). If they remove it in the next major release of PHP, there will be enough time to stop using mysql_* functions (and seriously nobody has been using it for years, it's only in legacy code) as it will take time (probably few years) until the new release is rolled into LTS releases.
7 年多之前 回复
weixin_41568126
乱世@小熊 As most production websites do not print errors, E_DEPRECATED is useless. Until "all" websites switch from mysql functions, it will not be removed. Even where I work, I have to work with mysql extension, because even they don't think it will be removed very soon. Maybe in php 6.0. Will see...
7 年多之前 回复
csdnceshi59
ℙℕℤℝ since 5.5 (which was released recently) the mysql_* functions already produce E_DEPRECATED warning. The ext/mysql extension has not been maintained for more then 10 years. Are you really so delusional?
7 年多之前 回复
weixin_41568126
乱世@小熊 They will not remove the mysql_* function from php, at least not very soon. Maybe in 2050. Think about it, if they remove it, all the servers that are doing automatic update of php will have all the websites nonfunctional. That's just absurd.
7 年多之前 回复
csdnceshi59
ℙℕℤℝ Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.
接近 8 年之前 回复
csdnceshi63
elliott.david It's horrible from an usability point of view though. Sometimes you couldn't use your main nickname/username/email-address and forget this after some time or the site deleted your account for inactivity. Then it's extremely annoying if you continue trying passwords and maybe even get your IP blocked even though it's just your username that is invalid.
9 年多之前 回复
csdnceshi73
喵-见缝插针 - I prefer not to give verbose errors like invalid user / invalid password... it tells brute force merchants that they have a valid user ID, and it's just the password they need to guess
9 年多之前 回复
csdnceshi79
python小菜 Always use prepared statements. The security provision, performance benefits of statement re-use, standardised coding, and library maintainance always (in my opinion) out-weigh any other alternative 'short-cut' method.
9 年多之前 回复
csdnceshi66
必承其重 | 欲带皇冠 I know, the above is just a simple example to get my point across.
9 年多之前 回复
csdnceshi63
elliott.david Usually it's better to do the password validation in the PHP code so you can display a more verbose error (invalid user / invalid password)
9 年多之前 回复

3个回答

Consider the following query:

$iId = mysql_real_escape_string("1 OR 1=1");    
$sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string() will not protect you against this. The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:

$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";
csdnceshi61
derek5. Great learning for me.Got my answer here.
2 年多之前 回复
csdnceshi50
三生石@ The query would look like `SELECT * FROM table WHERE login='aaa or 1=1 --' AND password='some_value' ; your payload value is the whole term and single quotes are put around the whole term, thus it is safe.
大约 3 年之前 回复
weixin_41568208
北城已荒凉 van Opdorp Why does single quoting protect from SQL injection? The query looks like this after expanding variables: SELECT * FROM table WHERE login='aaa' or 1=1 --' AND password='some_value' Why this is not a valid SQL statement?
3 年多之前 回复
csdnceshi57
perhaps? "The fact that you use single quotes (' ') around your variables inside your query is what protects you against this." Well but the single quotes were part of the example, the question asked for an attack for that situation, when you use single quotes.
4 年多之前 回复
weixin_41568196
撒拉嘿哟木头 Why did you remove the edit, @eggyal? Casting to int will produce a number, anyway. There can be nothing dangerous about numbers related to escaping, right? And Cast to integer itself does not care about any escaping.
4 年多之前 回复
csdnceshi60
℡Wang Yan Yet the answer is totally misleading. Obviously the question is asking about the contents within the quotes. "Quotes are not there" is not the answer to this question.
5 年多之前 回复
csdnceshi54
hurriedly% This is more of a misuse of the function than anything else. After all, it is named mysql_real_escape_string, not mysql_real_escape_integer . It's not mean to be used with integer fields.
大约 8 年之前 回复
csdnceshi80
胖鸭 (int)mysql_real_escape_string - this makes no sense. It doesn't differ from (int) at all. And they will produce the same result for every input
8 年多之前 回复
csdnceshi59
ℙℕℤℝ Although the usual example is DROP TABLE, in practice the attacker is more likely to SELECT passwd FROM users. In the latter case, the second query is usually executed by use of a UNION clause.
8 年多之前 回复
csdnceshi67
bug^君 But this wouldn't be a real problem, because mysql_query() doesn't execute multiple statements, no?
大约 9 年之前 回复
csdnceshi56
lrony* $iId = mysql_real_escape_string((int)"1; DROP table"); or `$dirty = "1; DROP table";$iId = mysql_real_escape_string((int)$dirty); would be a better example than what you have, I think.
大约 9 年之前 回复
csdnceshi51
旧行李 What if the users passes a single quote as part of the value: 1'; DROP TABLE -- the trailing comment will make the engine ignore the dangling other single quote from the statement
9 年多之前 回复

TL;DR

mysql_real_escape_string() will provide no protection whatsoever (and could furthermore munge your data) if:

  • MySQL's NO_BACKSLASH_ESCAPES SQL mode is enabled (which it might be, unless you explicitly select another SQL mode every time you connect); and

  • your SQL string literals are quoted using double-quote " characters.

This was filed as bug #72458 and has been fixed in MySQL v5.7.6 (see the section headed "The Saving Grace", below).

This is another, (perhaps less?) obscure EDGE CASE!!!

In homage to @ircmaxell's excellent answer (really, this is supposed to be flattery and not plagiarism!), I will adopt his format:

The Attack

Starting off with a demonstration...

mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"'); // could already be set
$var = mysql_real_escape_string('" OR 1=1 -- ');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

This will return all records from the test table. A dissection:

  1. Selecting an SQL Mode

    mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
    

    As documented under String Literals:

    There are several ways to include quote characters within a string:

    • A “'” inside a string quoted with “'” may be written as “''”.

    • A “"” inside a string quoted with “"” may be written as “""”.

    • Precede the quote character by an escape character (“\”).

    • A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

    If the server's SQL mode includes NO_BACKSLASH_ESCAPES, then the third of these options—which is the usual approach adopted by mysql_real_escape_string()—is not available: one of the first two options must be used instead. Note that the effect of the fourth bullet is that one must necessarily know the character that will be used to quote the literal in order to avoid munging one's data.

  2. The Payload

    " OR 1=1 -- 
    

    The payload initiates this injection quite literally with the " character. No particular encoding. No special characters. No weird bytes.

  3. mysql_real_escape_string()

    $var = mysql_real_escape_string('" OR 1=1 -- ');
    

    Fortunately, mysql_real_escape_string() does check the SQL mode and adjust its behaviour accordingly. See libmysql.c:

    ulong STDCALL
    mysql_real_escape_string(MYSQL *mysql, char *to,const char *from,
                 ulong length)
    {
      if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES)
        return escape_quotes_for_mysql(mysql->charset, to, 0, from, length);
      return escape_string_for_mysql(mysql->charset, to, 0, from, length);
    }
    

    Thus a different underlying function, escape_quotes_for_mysql(), is invoked if the NO_BACKSLASH_ESCAPES SQL mode is in use. As mentioned above, such a function needs to know which character will be used to quote the literal in order to repeat it without causing the other quotation character from being repeated literally.

    However, this function arbitrarily assumes that the string will be quoted using the single-quote ' character. See charset.c:

    /*
      Escape apostrophes by doubling them up
    
    // [ deletia 839-845 ]
    
      DESCRIPTION
        This escapes the contents of a string by doubling up any apostrophes that
        it contains. This is used when the NO_BACKSLASH_ESCAPES SQL_MODE is in
        effect on the server.
    
    // [ deletia 852-858 ]
    */
    
    size_t escape_quotes_for_mysql(CHARSET_INFO *charset_info,
                                   char *to, size_t to_length,
                                   const char *from, size_t length)
    {
    // [ deletia 865-892 ]
    
        if (*from == '\'')
        {
          if (to + 2 > to_end)
          {
            overflow= TRUE;
            break;
          }
          *to++= '\'';
          *to++= '\'';
        }
    

    So, it leaves double-quote " characters untouched (and doubles all single-quote ' characters) irrespective of the actual character that is used to quote the literal! In our case $var remains exactly the same as the argument that was provided to mysql_real_escape_string()—it's as though no escaping has taken place at all.

  4. The Query

    mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');
    

    Something of a formality, the rendered query is:

    SELECT * FROM test WHERE name = "" OR 1=1 -- " LIMIT 1
    

As my learned friend put it: congratulations, you just successfully attacked a program using mysql_real_escape_string()...

The Bad

mysql_set_charset() cannot help, as this has nothing to do with character sets; nor can mysqli::real_escape_string(), since that's just a different wrapper around this same function.

The problem, if not already obvious, is that the call to mysql_real_escape_string() cannot know with which character the literal will be quoted, as that's left to the developer to decide at a later time. So, in NO_BACKSLASH_ESCAPES mode, there is literally no way that this function can safely escape every input for use with arbitrary quoting (at least, not without doubling characters that do not require doubling and thus munging your data).

The Ugly

It gets worse. NO_BACKSLASH_ESCAPES may not be all that uncommon in the wild owing to the necessity of its use for compatibility with standard SQL (e.g. see section 5.3 of the SQL-92 specification, namely the <quote symbol> ::= <quote><quote> grammar production and lack of any special meaning given to backslash). Furthermore, its use was explicitly recommended as a workaround to the (long since fixed) bug that ircmaxell's post describes. Who knows, some DBAs might even configure it to be on by default as means of discouraging use of incorrect escaping methods like addslashes().

Also, the SQL mode of a new connection is set by the server according to its configuration (which a SUPER user can change at any time); thus, to be certain of the server's behaviour, you must always explicitly specify your desired mode after connecting.

The Saving Grace

So long as you always explicitly set the SQL mode not to include NO_BACKSLASH_ESCAPES, or quote MySQL string literals using the single-quote character, this bug cannot rear its ugly head: respectively escape_quotes_for_mysql() will not be used, or its assumption about which quote characters require repeating will be correct.

For this reason, I recommend that anyone using NO_BACKSLASH_ESCAPES also enables ANSI_QUOTES mode, as it will force habitual use of single-quoted string literals. Note that this does not prevent SQL injection in the event that double-quoted literals happen to be used—it merely reduces the likelihood of that happening (because normal, non-malicious queries would fail).

In PDO, both its equivalent function PDO::quote() and its prepared statement emulator call upon mysql_handle_quoter()—which does exactly this: it ensures that the escaped literal is quoted in single-quotes, so you can be certain that PDO is always immune from this bug.

As of MySQL v5.7.6, this bug has been fixed. See change log:

Functionality Added or Changed

Safe Examples

Taken together with the bug explained by ircmaxell, the following examples are entirely safe (assuming that one is either using MySQL later than 4.1.20, 5.0.22, 5.1.11; or that one is not using a GBK/Big5 connection encoding):

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 (and ircmaxell's too, 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 ircmaxell's 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.

Wrapping Up

Thus, if you:

  • use native prepared statements

OR

  • use MySQL v5.7.6 or later

OR

  • in addition to employing one of the solutions in ircmaxell's summary, use at least one of:

    • PDO;
    • single-quoted string literals; or
    • an explicitly set SQL mode that does not include NO_BACKSLASH_ESCAPES

...then you should be completely safe (vulnerabilities outside the scope of string escaping aside).

weixin_41568183
零零乙 I set sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES.Is that safe enough? Need I additional use mysql_query("SET SQL_MODE=''"); in PHP script?
接近 2 年之前 回复
csdnceshi66
必承其重 | 欲带皇冠 Nice breakdown.. i assume mysql->charset within the functions escape_quotes_for_mysql(mysql->charset, to, 0, from, length) and escape_string_for_mysql(mysql->charset, to, 0, from, length); is MySQL's default connecting charset or if used SET NAMES or anny kind of set_charset() function?
2 年多之前 回复
csdnceshi72
谁还没个明天 Our system relies on the 2nd safe example above. There are errors, where mysql_real_escape_string has been omitted. Fixing those in an emergency mode seems to be the prudent path, hoping we don't get nuked before the corrections. My rationale is converting to prepared statements will be a much longer process that will have to come after. Is the reason prepared statements is safer the fact that errors don't create vulnerabilities? In other words, is correctly implemented 2nd example above is just as safe as prepared statements?
3 年多之前 回复
csdnceshi69
YaoRaoLov my answer was a little broader, in that you can avoid this particular bug through PDO's quote() function—but prepared statements are a much safer and more appropriate way to avoid injection generally. Of course, if you have directly concatenated unescaped variables into your SQL then you are most certainly vulnerable to injection no matter what methods you use thereafter.
3 年多之前 回复
csdnceshi72
谁还没个明天 is using PDO sufficient, or is it necessary to use PDO prepared statements? Consider this in php/PDO: $sql="select * from product_master where abbr='".$_GET['prod']."'";// order by skey limit ".mt_rand(1,10).", 10"; $stmt = $pdo->query($sql); That would be wide open to injection, with no protection on the $_GET. No? Also, use of single quotes needs to be supported with mysqli_real_escape_string, is that right? If these questions seem too obvious, my problem is that I am reading the answer and I am not certain it says these things flat out. I can see the examples show this.
3 年多之前 回复
csdnceshi76
斗士狗 People shouldn't be using " for strings in the first place. SQL says that's for identifiers. But eh...just another example of MySQL saying "screw standards, i'll do whatever i want". (Fortunately, you can include ANSI_QUOTES in the mode to fix the quoting brokenness. The open disregard of standards, though, is a bigger issue that might require more severe measures.)
大约 6 年之前 回复
csdnceshi69
YaoRaoLov Er, I think the answer to both of those questions is "yes" - although I suspect many won't consider this to be a vulnerability per se, but rather a design flaw in separating escaping from quoting. In the bug report I have proposed a fix, but as it changes the protocol (albeit in a safe way) I don't know whether the fix will be adopted.
6 年多之前 回复
csdnceshi61
derek5. I'm not able to access bugs.mysql.com/bug.php?id=72458; I just get an access denied page. Is it being hidden from the public due to being a security issue? Also, do I understand correctly from this answer that you are the discoverer of the vulnerability? If so, congratulations.
6 年多之前 回复
csdnceshi69
YaoRaoLov Yes, thank you! I will add that.
6 年多之前 回复
csdnceshi64
游.程 So, TL;DR would be like "there is a NO_BACKSLASH_ESCAPES mysql server mode which can cause an injection if you aren't using single quotes.
6 年多之前 回复

Well, there's nothing really that can pass through that, other than % wildcard. It could be dangerous if you were using LIKE statement as attacker could put just % as login if you don't filter that out, and would have to just bruteforce a password of any of your users. People often suggest using prepared statements to make it 100% safe, as data can't interfere with the query itself that way. But for such simple queries it probably would be more efficient to do something like $login = preg_replace('/[^a-zA-Z0-9_]/', '', $login);

csdnceshi50
三生石@ Users just want to get shit done. Security directly opposes that in most cases, and you can safely assume it will be avoided or subverted by any means possible. People aren't going to pick a half dozen random imaginary words; they're going to pick a short line/sentence/catchphrase from their favorite book or movie or whatever, making the phrase much, much more predictable. In order to prevent them from doing such things, you'd basically have to make the server understand English and/or search a database of most known artistic works for the phrase used.
7 年多之前 回复
csdnceshi50
三生石@ Most site owners hardly even realize there are server logs, much less read through them. And a non-idiot attacker wouldn't steal the data, but simply copy it...leaving the site functional and your average owner not even realizing there was an intrusion (and thus, not knowing to reset passwords), giving the attacker lots of time to crack the easy phrases. Human nature effectively guarantees there will be easy phrases, especially if dictionary words are allowed. And without dictionary words, all you've done is require a 20+-character password, which the users would hate you for. :)
7 年多之前 回复
csdnceshi55
~Onlooker And if you use self-made words, leaving attacker with need to try by char... 22 chars with 6 bits/char (lower-upper case, dashes, etc. = roughly 64) ~= 5.4 duodecillion = years
接近 8 年之前 回复
csdnceshi55
~Onlooker sorry to go on with this holy war here, but still... Let's say you're pretty good on resources and can try 100 million passwords/sec. Using 3k-words dictionary and knowing there are 4 words trying 3.3 trillion passwords would take you about 23 days. That will be enough for host to notice stolen DB and change your password. Safe enough. You can make that many times harder by alternating lower/upper-case letters. And all this assuming attacker knows there are exactly 4 words, which is unlikely.
接近 8 年之前 回复
csdnceshi50
三生石@ I've seen that idea before; see xkcd.com/936 . Problem is, the math doesn't quite bear it out. Your example 17-char password would have like 96^17 possibilities, and that's if you forgot the umlauts and limited yourself to printable ASCII. That's about 4.5x10^33. We're talking literally a billion trillion times more work to brute force. Even an 8-char ASCII password would have 7.2x10^15 possibilities -- 3 thousand times more.
接近 8 年之前 回复
csdnceshi55
~Onlooker would be much harder to bruteforce. Even using a dictionary of, say 3000 words to help you, knowing you used exactly 4 words - that would still be roughly 3.3*10^12 combinations. :)
接近 8 年之前 回复
csdnceshi50
三生石@ You're effectively limiting usernames and passwords to word chars only. Most people who know anything about security would consider that a bad idea, as it shrinks the search space considerably. Course they'd also consider it a bad idea to store cleartext passwords in the database, but we don't need to be compounding the problem. :)
接近 8 年之前 回复
csdnceshi64
游.程 By what measure do you consider a simple replacement more efficient than using prepared statements? (Prepared statements always work, the library can be quickly corrected in case of attacks, doesn't expose human error [such as mis-typing the complete replace string], and have significant performance benefits if the statement is re-used.)
9 年多之前 回复
weixin_41568174
from.. +1, but the wildcards are for LIKE clause, not simple equality.
9 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐