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 ".在哪里 " .mysql_real_escape_string($field) .=".mysql_real_escape_string($value);

这样可以安全准确地逃脱吗?不！为什么?因为黑客很可能仍然这样做:

跟我重复:

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

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

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

这里有一些示例代码来演示所有这些:

//包含类 DBConfig;数据库信息.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);而 (($data = mysql_fetch_array($qq))){打印_r($数据);}

这是传递各种输入时此代码的结果:

$ php sql_exploits.php url http://www.reddit.com idSQL 生成:SELECT url FROM GrabbedURLsWHERE url LIKE 'http://www.reddit.com%'按 id 排序；返回:仅以http://www.reddit.com"开头的 URL$ php sql_exploits.php url % idSQL 生成:SELECT url FROM GrabbedURLsWHERE url LIKE '%%'按 id 排序；结果:返回每个结果 不是你编程的，因此是一个漏洞——

<块引用>

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

然后是非常讨厌的 LIMIT 漏洞:

$ php sql_exploits.php url>'http://www.reddit.com'>从缓存域中联合选择名称"生成的 SQL:SELECT url FROM GrabbedURLsWHERE url LIKE 'http://reddit.com%'限制 1联盟从缓存域中选择名称；返回:一个完全出乎意料的，可能(可能)未经授权的查询来自另一个完全不同的表.

是否了解攻击中的 SQL 无关紧要.这表明即使是最不成熟的黑客也轻松绕过 mysql_real_escape_string().那是因为它是一种反应性防御机制.它只修复了数据库中非常有限和已知的漏洞.

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

正确且唯一(真正)的防御是主动防御:使用准备好的陈述.准备好的语句经过精心设计，以便仅执行有效且已编程的 SQL.这意味着，如果操作正确，能够执行意外 SQL 的几率会大大降低.

理论上，完美实现的准备好的语句不会受到所有已知和未知攻击的影响，因为它们是一种服务器端技术，由数据库服务器自身和与编程语言接口的库处理.因此，您始终可以保证至少受到保护，免受所有已知的黑客攻击.

而且代码更少:

$pdo = new PDO($dsn);$column = 'url';$value = 'http://www.stackoverflow.com/';$limit = 1;$validColumns = array('url', 'last_fetched');//确保验证 $column 是否是有效的搜索参数.//如果是无效列，则默认为 'id'.if (!in_array($column, $validColumns) { $column = 'id'; }$statement = $pdo->prepare('SELECT url FROM GrabbedURLs ' .'在哪里 ' .$列.'=?' .'限制 ' .整数($限制))；$statement->execute(array($value));while (($data = $statement->fetch())) { }

那不是那么难吗?而且它减少了 47% 的代码(195 个字符 (PDO) 与 375 个字符 (mysql_)).这就是我所说的，充满胜利".

为了解决这个答案引起的所有争议，请允许我重申我已经说过的内容:

<块引用>

使用准备好的语句可以利用保护措施SQL 服务器本身，因此你受到保护，免受那些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屋！