mysql_real_escape_string()和mysql_escape_string()是否足以确保应用安全? [英] Are mysql_real_escape_string() and mysql_escape_string() sufficient for app security?

问题描述

mysql_real_rescape_string()是否足以保护我免受黑客和SQL攻击?问我是因为听说这些方法不能帮助您抵御所有攻击媒介?寻求专家的建议.

另外，LIKE SQL攻击又如何呢?

解决方案

@Charles是非常正确的！

您面临多种已知 SQL攻击的风险，包括您提到的

• SQL注入:是的！ Mysql_Escape_String可能仍然使您容易受到SQL注入的影响，具体取决于您在查询中使用PHP变量的位置.

考虑一下:

$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);  

那样可以安全，准确地逃脱吗?不！为什么?因为黑客仍然可以很好地做到这一点:

在我之后重复:

mysql_real_escape_string()仅用于转义变量数据， NOT 表名，列名，尤其是LIMIT字段.

• LIKE漏洞:LIKE"$ data％"，其中$ data可能是％"，它将返回所有记录... 很可能是一个安全漏洞...只是想象通过信用卡的后四位数字进行查找...糟糕！现在，黑客可能会收到您系统中的每个信用卡号！ (顺便说一句:几乎不建议存储完整的信用卡！)

• Charset漏洞:无论仇恨者怎么说，Internet Explorer仍然很强大，在2011年，很容易受到字符集漏洞的攻击，这就是您设计的 if 您的HTML页面正确，相当于<meta name="charset" value="UTF-8"/>！这些攻击非常讨厌，因为它们给黑客提供了与直接SQL注入一样多的控制权:满的.

下面是一些示例代码来演示所有这些内容:

// Contains class DBConfig; database information.
require_once('../.dbcreds');                       

$dblink = mysql_connect(DBConfig::$host, DBConfig::$user, DBConfig::$pass);
mysql_select_db(DBConfig::$db);
//print_r($argv);

$sql = sprintf("SELECT url FROM GrabbedURLs WHERE %s LIKE '%s%%' LIMIT %s",
               mysql_real_escape_string($argv[1]),
               mysql_real_escape_string($argv[2]),
               mysql_real_escape_string($argv[3]));
echo "SQL: $sql\n";
$qq = mysql_query($sql);
while (($data = mysql_fetch_array($qq)))
{
        print_r($data);
}

下面是传递各种输入时的代码结果:

$ php sql_exploits.php url http://www.reddit.com id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://www.reddit.com%'
               ORDER BY id;
Returns: Just URLs beginning w/ "http://www.reddit.com"

$ php sql_exploits.php url % id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE '%%' 
               ORDER BY id;
Results: Returns every result Not what you programmed, ergo an exploit --

$ php sql_exploits.php 1 = 1 ' http://www.reddit.com 'id结果: 返回每一列和每个结果.

然后有一些非常令人讨厌的LIMIT漏洞利用

$ php sql_exploits.php url 
> 'http://www.reddit.com'
> "UNION SELECT name FROM CachedDomains"
Generated SQL: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://reddit.com%' 
               LIMIT 1 
               UNION
               SELECT name FROM CachedDomains;
Returns:  An entirely unexpected, potentially (probably) unauthorized query
          from another, completely different table. 

您是否了解攻击中的SQL是不明智的.这表明，即使最不成熟的黑客也很容易绕过mysql_real_escape_string().那是因为它是一种反应式防御机制.它只能修复数据库中非常有限且已知的漏洞.

所有转义都永远不足以保护数据库.实际上，您可以对每个已知的漏洞进行明确的反应，并且将来，您的代码很可能容易受到将来发现的攻击的攻击.<​​/p>

适当且唯一(实际上)的防御是一种主动:使用准备好的陈述.对准备好的语句的设计要特别谨慎，以便仅执行有效的和已编程的SQL.这意味着，如果正确完成操作，那么可以意外地执行意外SQL的可能性就会大大降低.

从理论上讲，可以很好地实现的准备好的语句将不受所有已知和未知的攻击的侵害，因为它们是SERVER SIDE技术，由DATABASE SERVERS THEMSELVES和与编程语言接口的库处理.因此，我们始终保证您会受到保护，免受任何最低程度的威胁.

更少的代码:

$pdo = new PDO($dsn);

$column = 'url';
$value = 'http://www.stackoverflow.com/';
$limit = 1;

$validColumns = array('url', 'last_fetched');

// Make sure to validate whether $column is a valid search parameter.
// Default to 'id' if it's an invalid column.
if (!in_array($column, $validColumns) { $column = 'id'; }


$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));
while (($data = $statement->fetch())) { }

现在不是那么难吗?而且它的代码减少了百分之四十七(分别为195个字符(PDO)和375个字符(mysql_).这就是我所说的充满胜利".

要解决此答案引起的所有争议，请允许我重申我已经说过的话:

使用准备好的陈述可以使人们利用以下方面的保护措施: SQL Server本身，因此 您受到保护，免受 人们对SQL Server有所了解.因为 在这种额外的保护级别中，您 比仅使用安全得多 逃脱，无论多么彻底.

Will mysql_real_rescape_string() be enough to protect me from hackers and SQL attacks? Asking because I heard that these don't help against all attack vectors? Looking for the advice of experts.

EDIT: Also, what about LIKE SQL attacks?

解决方案

@Charles is extremely correct!

You put yourself at risk for multiple types of known SQL attacks, including, as you mentioned

• SQL injection: Yes! Mysql_Escape_String probably STILL keeps you susceptible to SQL injections, depending on where you use PHP variables in your queries.

Consider this:

$sql = "SELECT number FROM PhoneNumbers " .
       "WHERE " . mysql_real_escape_string($field) . " = " . mysql_real_escape_string($value);  

Can that be securely and accurately escaped that way? NO! Why? because a hacker could very well still do this:

Repeat after me:

mysql_real_escape_string() is only meant to escape variable data, NOT table names, column names, and especially not LIMIT fields.

• LIKE exploits: LIKE "$data%" where $data could be "%" which would return ALL records ... which can very well be a security exploit... just imagine a Lookup by last four digits of a credit card... OOPs! Now the hackers can potentially receive every credit card number in your system! (BTW: Storing full credit cards is hardly ever recommended!)

• Charset Exploits: No matter what the haters say, Internet Explorer is still, in 2011, vulnerable to Character Set Exploits, and that's if you have designed your HTML page correctly, with the equivalent of <meta name="charset" value="UTF-8"/>! These attacks are VERY nasty as they give the hacker as much control as straight SQL injections: e.g. full.

Here's some example code to demonstrate all of this:

// Contains class DBConfig; database information.
require_once('../.dbcreds');                       

$dblink = mysql_connect(DBConfig::$host, DBConfig::$user, DBConfig::$pass);
mysql_select_db(DBConfig::$db);
//print_r($argv);

$sql = sprintf("SELECT url FROM GrabbedURLs WHERE %s LIKE '%s%%' LIMIT %s",
               mysql_real_escape_string($argv[1]),
               mysql_real_escape_string($argv[2]),
               mysql_real_escape_string($argv[3]));
echo "SQL: $sql\n";
$qq = mysql_query($sql);
while (($data = mysql_fetch_array($qq)))
{
        print_r($data);
}

Here's the results of this code when various inputs are passed:

$ php sql_exploits.php url http://www.reddit.com id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://www.reddit.com%'
               ORDER BY id;
Returns: Just URLs beginning w/ "http://www.reddit.com"

$ php sql_exploits.php url % id
SQL generated: SELECT url FROM GrabbedURLs 
               WHERE url LIKE '%%' 
               ORDER BY id;
Results: Returns every result Not what you programmed, ergo an exploit --

$ php sql_exploits.php 1=1 'http://www.reddit.com' id Results: Returns every column and every result.

Then there are the REALLLY nasty LIMIT exploits:

$ php sql_exploits.php url 
> 'http://www.reddit.com'
> "UNION SELECT name FROM CachedDomains"
Generated SQL: SELECT url FROM GrabbedURLs 
               WHERE url LIKE 'http://reddit.com%' 
               LIMIT 1 
               UNION
               SELECT name FROM CachedDomains;
Returns:  An entirely unexpected, potentially (probably) unauthorized query
          from another, completely different table. 

Whether you understand the SQL in the attacks or not is irrevelant. What this has demonstrated is that mysql_real_escape_string() is easily circumvented by even the most immature of hackers. That is because it is a REACTIVE defense mechism. It only fixes very limited and KNOWN exploits in the Database.

All escaping will NEVER be sufficient to secure databases. In fact, you can explicitly REACT to every KNOWN exploit and in the future, your code will most likely become vulnerable to attacks discovered in the future.

The proper, and only (really) , defense is a PROACTIVE one: Use Prepared Statements. Prepared statements are designed with special care so that ONLY valid and PROGRAMMED SQL is executed. This means that, when done correctly, the odds of unexpected SQL being able to be executed are drammatically reduced.

Theoretically, prepared statements that are implemented perfectly would be impervious to ALL attacks, known and unknown, as they are a SERVER SIDE technique, handled by the DATABASE SERVERS THEMSELVES and the libraries that interface with the programming language. Therefore, you're ALWAYS guaranteed to be protected against EVERY KNOWN HACK, at the bare minimum.

And it's less code:

$pdo = new PDO($dsn);

$column = 'url';
$value = 'http://www.stackoverflow.com/';
$limit = 1;

$validColumns = array('url', 'last_fetched');

// Make sure to validate whether $column is a valid search parameter.
// Default to 'id' if it's an invalid column.
if (!in_array($column, $validColumns) { $column = 'id'; }


$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .
                           'WHERE ' . $column . '=? ' .
                           'LIMIT ' . intval($limit));
$statement->execute(array($value));
while (($data = $statement->fetch())) { }

Now that wasn't so hard was it? And it's forty-seven percent less code (195 chars (PDO) vs 375 chars (mysql_). That's what I call, "full of win".

EDIT: To address all the controversy this answer stirred up, allow me to reiterate what I have already said:

Using prepared statements allows one to harness the protective measures of the SQL server itself, and therefore you are protected from things that the SQL server people know about. Because of this extra level of protection, you are far safer than by just using escaping, no matter how thorough.

这篇关于mysql_real_escape_string()和mysql_escape_string()是否足以确保应用安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！