混合输入是否足以清除SQL查询? [英] Is hexing input sufficient to sanitize SQL Queries?
问题描述
昨晚我在阅读有关防止SQL注入的内容，我碰到了这个答案:
您的常识"中的评论听起来像是功能失调/不安全.但是，在我的测试(尽管很有限)中，我发现php的"bin2hex($ var)"可以处理我扔给它的所有内容-文字数字，数字字符串，文本字符串-即使与数字(tinyint)列匹配也是如此./p>
我的问题是:通过混合混合清理每个用户输入时，是否有一种方法可以注入SQL?从本质上讲，每当进行查询时，它都将看起来像这样:
$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"
基本上翻译为:
SELECT * FROM table WHERE someidentifier=UNHEX('0b99f') LIMIT 1
这种安全性是否存在漏洞?
PS-我不仅在寻找诸如为什么不将PDO或MySQLi与准备好的语句一起使用?"之类的答案?它可能会陷入抢先式优化的巨大弊端，但我不希望将查询开销加倍(是的，我确实知道，使用多个相同的查询它可以更快，但这不是我经常遇到的情况).
当通过混合混合清理每个用户输入时，是否有一种方法可以注入SQL?
如果您知道为什么会发生SQL注入，则可以自己回答这个问题.
让我们看看. CWE描述SQL注入(CWE-89)如下:
该软件使用受外部影响的输入[…]构造全部或部分SQL命令，但不会中和或错误地中和了可能修改预期SQL命令[...]的特殊元素.
此外:
在用户可控制的输入中没有充分删除或引用SQL语法的情况下，生成的SQL查询可能导致这些输入被解释为SQL，而不是普通的用户数据.
因此，基本上:生成的SQL查询中的外部影响输入未按预期进行解释.这里的重要部分是:未按预期解释.
如果要将用户输入解释为 MySQL字符串文字但不是，这是SQL注入.但是为什么会发生呢?
好吧，字符串文字具有某种语法，可用来识别它们通过SQL解析器:
字符串是由字节或字符组成的序列，用单引号("
'")或双引号(""")字符括起来.
另外:
在字符串中，某些序列具有特殊含义[…].这些序列中的每个序列都以反斜杠("
\")开头，称为转义字符. MySQL识别表9.1，特殊字符转义"中显示的转义序列序列" .
此外，为了能够在字符串文字中使用引号:
有几种方法可以在字符串中包含引号字符:
- 用"
'"引用的字符串中的"'"可以写为"''".- 用"
""引用的字符串中的"""可以写为"""".- 在引号字符前加转义字符("
\").- 用"
""引用的字符串中的"'"不需要特殊处理，无需加倍或转义.以同样的方式，用引号"'"括起来的字符串中的"""不需要特殊处理.
由于所有这些后面提到的序列都是字符串文字所特有的，因此有必要对任何要解释为字符串文字的数据进行适当处理，以符合这些规则.这尤其意味着:如果要在字符串文字中使用任何提及的字符，则必须以提及的一种方式编写它们.
因此，如果您以这种方式看待它，它甚至不是安全问题，而仅仅是处理数据以使数据被解释为预期的.
这同样适用于其他文字以及SQL的其他方面.
那你的问题呢?
我的问题是:通过混合混合清理每个用户输入时，是否有一种方法可以注入SQL?从本质上讲，每当进行查询时，它都将看起来像这样:
$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"
是的，这对于SQL注入是安全的. bin2hex返回仅包含十六进制字符的字符串.在MySQL字符串文字中使用这些字符时，这两个字符均不需要特殊处理.
但是认真的说，当有库和框架提供诸如参数化/准备好的语句之类的便捷技术时，为什么有人会想使用这些繁琐的格式化技术呢?
I was reading last night on preventing SQL injections, and I ran across this answer:
How can I prevent SQL injection in PHP?
The comments from 'Your Common Sense' made it sound like that was dysfunctional/unsafe. However, in my (albeit limited) testing, I found that php's "bin2hex($var)" worked with anything I threw at it - literal number, number string, string of text - even when matching a numerical (tinyint) column.
My question is this: Is there a way to inject SQL when every user input is sanitized via hexing it? In essence, any time a query was made, it would look something like this:
$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"
Basically translating to:
SELECT * FROM table WHERE someidentifier=UNHEX('0b99f') LIMIT 1
Are there any holes in this type of security?
PS - I'm not just looking for answers like "Why not just use PDO or MySQLi with prepared statements?" It may fall under the vast evil of preemptive optimization, but I'd rather not double my query overhead (and yes, I do understand that it can be faster with multiple identical queries, but that's not a situation I often encounter).
Is there a way to inject SQL when every user input is sanitized via hexing it?
If you knew why an SQL injection occurs, you would be able to answer this question yourself.
Let’s see. The CWE describes SQL injections (CWE-89) as follows:
The software constructs all or part of an SQL command using externally-influenced input […], but it does not neutralize or incorrectly neutralizes special elements that could modify the intended SQL command […]
Furthermore:
Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data.
So basically: externally-influenced inputs in a generated SQL query are not interpreted as intended. The important part here is: not interpreted as intended.
If a user input is intended to be interpreted as a MySQL string literal but it isn’t, it’s an SQL injection. But why does it happen?
Well, string literals have a certain syntax by which they are identified by the SQL parser:
A string is a sequence of bytes or characters, enclosed within either single quote ("
'") or double quote (""") characters.
Additionally:
Within a string, certain sequences have special meaning […]. Each of these sequences begins with a backslash ("
\"), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, "Special Character Escape Sequences".
Additionally, to be able to use quotes within 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.
As all these latter mentioned sequences are special to string literals, it is necessary that any data, that is intended to be interpreted as a string literal, is properly processed to conform to these rules. This means in particular: if any of the mentioned characters are intended to be used in a string literal, they have to be written as one of the mentioned ways.
So if you look at it from this way, it is not even a question of security but simply of processing data so that they are being interpreted as intended.
The same applies to the other literals as well as other aspects of SQL.
So what about your question?
My question is this: Is there a way to inject SQL when every user input is sanitized via hexing it? In essence, any time a query was made, it would look something like this:
$query="SELECT * FROM table WHERE someidentifier=UNHEX('".bin2hex($unsafe_user_input)."') LIMIT 1"
Yes, that would be safe from SQL injections. bin2hex returns a string that contains only hexadecimal characters. And neither of these characters require a special treatment when using them in a MySQL string literal.
But seriously, why would anyone want to use these cumbersome formatting technique when there libraries and frameworks that supply convenient techniques like parameterized/prepared statements?
这篇关于混合输入是否足以清除SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
