很多 web 开发者没有注意到 SQL 查询是可以被篡改的，因而把 SQL 查询当作可信任的命令。殊不知道，SQL 查询可以绕开访问控制，从而绕过身份验证和权限检查。更有甚者，有可能通过 SQL 查询去运行主机操作系统级的命令。
直接 SQL 命令注入就是攻击者常用的一种创建或修改已有 SQL 语句的技术，从而达到取得隐藏数据，或覆盖关键的值，甚至执行数据库主机操作系统命令的目的。这是通过应用程序取得用户输入并与静态参数组合成 SQL 查询来实现的。下面将会给出一些真实的例子。
由于在缺乏对输入的数据进行验证，并且使用了超级用户或其它有权创建新用户的数据库帐号来连接，攻击者可以在数据库中新建一个超级用户。
Example #1 一段实现数据分页显示的代码……也可以被用作创建一个超级用户（PostgreSQL系统）。
<?php
$offset = $argv[0]; // 注意，没有输入验证！
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
$result = pg_query($conn, $query);
?>
0;
insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
select 'crack', usesysid, 't','t','crack'
from pg_shadow where usename='postgres';
--
Note:
-- 是 SQL 的注释标记，一般可以使用来它告诉 SQL 解释器忽略后面的语句。
对显示搜索结果的页面下手是一个能得到密码的可行办法。攻击者所要做的只不过是找出哪些提交上去的变量是用于 SQL 语句并且处理不当的。而这类的变量通常都被用于 SELECT 查询中的条件语句，如 WHERE, ORDER BY, LIMIT 和 OFFSET。如果数据库支持 UNION 构造的话，攻击者还可能会把一个完整的 SQL 查询附加到原来的语句上以便从任意数据表中得到密码。因此，对密码字段加密是很重要的。
Example #2 显示文章……以及一些密码（任何数据库系统）
<?php
$query = "SELECT id, name, inserted, size FROM products
WHERE size = '$size'
ORDER BY $order LIMIT $limit, $offset;";
$result = odbc_exec($conn, $query);
?>
' union select '1', concat(uname||'-'||passwd) as name, '1971-01-01', '0' from usertable; --
SQL 中的 UPDATE 也会受到攻击。这种查询也可能像上面的例子那样被插入或附加上另一个完整的请求。但是攻击者更愿意对 SET 子句下手，这样他们就可以更改数据表中的一些数据。这种情况下必须要知道数据库的结构才能修改查询成功进行。可以通过表单上的变量名对字段进行猜测，或者进行暴力破解。对于存放用户名和密码的字段，命名的方法并不多。
Example #3 从重设密码……到获得更多权限（任何数据库系统）
<?php
$query = "UPDATE usertable SET pwd='$pwd' WHERE uid='$uid';";
?>
<?php
// $uid == ' or uid like'%admin%'; --
$query = "UPDATE usertable SET pwd='...' WHERE uid='' or uid like '%admin%'; --";
// $pwd == "hehehe', admin='yes', trusted=100 "
$query = "UPDATE usertable SET pwd='hehehe', admin='yes', trusted=100 WHERE
...;";
?>
下面这个可怕的例子将会演示如何在某些数据库上执行系统命令。
Example #4 攻击数据库所在主机的操作系统（MSSQL Server）
<?php
$query = "SELECT * FROM products WHERE id LIKE '%$prod%'";
$result = mssql_query($query);
?>
<?php
$query = "SELECT * FROM products
WHERE id LIKE '%a%'
exec master..xp_cmdshell 'net user test testpass /ADD'--";
$result = mssql_query($query);
?>
Note:
虽然以上的例子是针对某一特定的数据库系统的，但是这并不代表不能对其它数据库系统实施类似的攻击。使用不同的方法，各种数据库都有可能遭殃。
也许有人会自我安慰，说攻击者要知道数据库结构的信息才能实施上面的攻击。没错，确实如此。但没人能保证攻击者一定得不到这些信息，一但他们得到了，数据库有泄露的危险。如果你在用开放源代码的软件包来访问数据库，比如论坛程序，攻击者就很容得到到相关的代码。如果这些代码设计不良的话，风险就更大了。
这些攻击总是建立在发掘安全意识不强的代码上的。所以，永远不要信任外界输入的数据，特别是来自于客户端的，包括选择框、表单隐藏域和 cookie。就如上面的第一个例子那样，就算是正常的查询也有可能造成灾难。
如果程序等待输入一个数字，可以考虑使用 is_numeric() 来检查，或者直接使用 settype() 来转换它的类型，也可以用 sprintf() 把它格式化为数字。
Example #5 一个实现分页更安全的方法
<?php
settype($offset, 'integer');
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
// 请注意格式字符串中的 %d，如果用 %s 就毫无意义了
$query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
$offset);
?>
除此之外，在允许的情况下，使用代码或数据库系统保存查询日志也是一个好办法。显然，日志并不能防止任何攻击，但利用它可以跟踪到哪个程序曾经被尝试攻击过。日志本身没用，要查阅其中包含的信息才行。毕竟，更多的信息总比没有要好。
Richard dot Corfield at gmail dot com (2011-11-18 14:01:57)
The best way has got to be parameterised queries. Then it doesn't matter what the user types in the data goes to the database as a value.
A quick search online shows some possibilities in PHP which is great! Even on this site - http://php.net/manual/en/pdo.prepared-statements.php
which also gives the reasons this is good both for security and performance.
smunday at visionaryweb dot com (2010-12-17 14:58:50)
Another suggestion would be to build a series of DB procedures / functions that you give the DB user access to to manipulate or select data. That way, all input would run through this exposed interface and all parameters are forced to be typecast (or rejected).
Anonymous (2010-09-26 21:16:58)
Pangolin is an automatic SQL injection penetration testing tool developed by NOSEC.
Its goal is to detect and take advantage of SQL injection vulnerabilities on web applications. Once it detects one or more SQL injections on the target host, the user can choose among a variety of options to perform an extensive back-end database management system fingerprint, retrieve DBMS session user and database, enumerate users, password hashes, privileges, databases, dump entire or user"s specific DBMS tables/columns, run his own SQL statement, read specific files on the file system and more.
wang dot liang dot com at gmail dot com (2010-03-11 15:11:13)
another way to stop sql injection when you odbc_*: create two users,
one has only select permission,
the other has only delete, update, and insert permission,
so you can use select-only user to call odbc_exec while you don't have to check the sql injection; and you use d/u/i only user to update database by calling odbc_prepare and odbc_execute.
fyrye (2009-08-06 15:59:13)
Another way to prevent SQL injections as opposed to binary, is to use URL Encoding or Hex Encoding.
I haven't seen a complete example of stopping SQL Injections, most refer to use the mysql_real_escape_string function or param statements.
Several examples at http://en.wikipedia.org/wiki/SQL_injection
Which will stop \x00, \n, \r, \, ', " and \x1a based attacks.
Alot depends on your SQL query structure, though vector level attacks are still viable.
Other than that build your own regex replacement to protect specific queries that could alter or compromise your database/results for specific sections of your processing pages.
Also use unique table and field names. Not just putting _ infront of them...
Example, don't store User/s or Customer/s information in a table named the same.
And NEVER use the same form field names for database field names.
bendikt [at] armed [dot] nu (2009-07-27 07:31:22)
i just played around with the array_walk function.
It suddenly struck me that almost all super globals are arrays.
So what i discovered was that i can apply the array_walk function to the super globals. Doing so you automatically run a function call through the super globals
With this piece of code i wrote you should be able to secure most of you input data.
<?php
class secure
{
function secureSuperGlobalGET(&$value, $key)
{
$_GET[$key] = htmlspecialchars(stripslashes($_GET[$key]));
$_GET[$key] = str_ireplace("script", "blocked", $_GET[$key]);
$_GET[$key] = mysql_escape_string($_GET[$key]);
return $_GET[$key];
}
function secureSuperGlobalPOST(&$value, $key)
{
$_POST[$key] = htmlspecialchars(stripslashes($_POST[$key]));
$_POST[$key] = str_ireplace("script", "blocked", $_POST[$key]);
$_POST[$key] = mysql_escape_string($_POST[$key]);
return $_POST[$key];
}
function secureGlobals()
{
array_walk($_GET, array($this, 'secureSuperGlobalGET'));
array_walk($_POST, array($this, 'secureSuperGlobalPOST'));
}
}
?>
Note that you can modify this in anyway to suit your needs.
The Script has been tested.
Quietust (2009-06-02 17:23:37)
An anonymous comment below suggests using PEAR with prepare() / execute() - though it was posted 3 years ago, it is still true today, though it's even easier now since PDO is included in most distributions. For SET/WHERE clauses and INSERT statements, just prepare the query with question marks in place of the dynamic parameters, bind each value in, then execute it, and it'll do all of the escaping for you, rendering the query immune to injection. Dynamic substitution of ORDER BY or LIMIT clauses has to be done the old fashioned way, though, so you still need to be careful with those.
Even without PDO, if you're using Postgres, you've already got the means to use parameterized queries, and if you're using MySQL, you simply need to ignore the outdated "mysql" extension and use "mysqli" instead.
fOV (2008-11-12 04:53:16)
The best prevention is to deactivate master..xp_cmdshell.
Run in isql the command `sp_configure 'xp_cmdshell''
If the value for "config value" is 1 then make in the isql
`sp_configure 'xp_cmdshell',0'.
If you want see the options from your mssql-Server make
`sp_configure 'show advanced options',1'
and then `sp_configure'
jaimthorn at yahoo dot com (2008-10-13 02:32:11)
dark dot avenger at email dot cz wrote:
"I think that easy way to protect against SQL injection is to convert inputted data into binary format, so that whatever input is, in sql query it will consist only of 1s and 0s."
Unless there is a 1-to-1 correspondence between your inputted data and the characters in your 'binary' format, a SELECT query wouldn't work anymore. Not a binary format, but it makes my point: MIME encoding the text 'Dark Avenger' results in 'RGFyayBBdmVuZ2Vy'. If I wanted to look up anyone with 'Avenger' in his/her name, then 'Avenger' would be encoded as 'QXZlbmdlcg==' which clearly wouldn't result in a hit on 'RGFyayBBdmVuZ2Vy'.
If there IS a 1-to-1 correspondence, then EITHER your solution only makes it a bit harder to perform a SQL injection (a hacker would have to figure out what mapping was used between the text and the 'binary' format), OR you've come up with simply another way to escaping your data. Either isn't a terribly good solution to the SQL injection problem.
valerylourie at gmail dot com (2008-04-15 00:23:33)
Note that PHP 5 introduced filters that you can use for untrusted user input:
http://us.php.net/manual/en/intro.filter.php
ctm at etheon dot net (2006-08-01 08:39:27)
This is a very helpful document from the MySQL site (in .pdf format) :
http://dev.mysql.com/tech-resources/articles/
guide-to-php-security-ch3.pdf