from web src

MySQL - SQL Injection Prevention

If you have ever taken raw user input and inserted it into a MySQL database there's a chance that you have left yourself wide open for a security issue known as SQL Injection. This lesson will teach you how to help prevent this from happening and help you secure your scripts and MySQL statements.

What is SQL Injection

SQL injection refers to the act of someone inserting a MySQL statement to be run on your database without your knowledge. Injection usually occurs when you ask a user for input, like their name, and instead of a name they give you a MySQL statement that you will unknowingly run on your database.

SQL Injection Example

Below is a sample string that has been gathered from a normal user and a bad user trying to use SQL Injection. We asked the users for their login, which will be used to run a SELECT statement to get their information.

// a good user's name $name = "timmy"; $query = "SELECT * FROM customers WHERE username = '$name'"; echo "Normal: " . $query . "
"; // user input that uses SQL Injection $name_bad = "' OR 1'"; // our MySQL query builder, however, not a very safe one $query_bad = "SELECT * FROM customers WHERE username = '$name_bad'"; // display what the new query will look like, with injection echo "Injection: " . $query_bad;

Display:

The normal query is no problem, as our MySQL statement will just select everything from customers that has a username equal to timmy.

However, the injection attack has actually made our query behave differently than we intended. By using a single quote (') they have ended the string part of our MySQL query

• username = ' '

and then added on to our WHERE statement with an OR clause of 1 (always true).

• username = ' ' OR 1

This OR clause of 1 will always be true and so every single entry in the "customers" table would be selected by this statement!

More Serious SQL Injection Attacks

Although the above example displayed a situation where an attacker could possibly get access to a lot of information they shouldn't have, the attacks can be a lot worse. For example an attacker could empty out a table by executing a DELETE statement.

$name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; // our MySQL query builder really should check for injection $query_evil = "SELECT * FROM customers WHERE username = '$name_evil'"; // the new evil injection query would include a DELETE statement echo "Injection: " . $query_evil;

Display:

If you were run this query, then the injected DELETE statement would completely empty your "customers" table. Now that you know this is a problem, how can you prevent it?

Injection Prevention - mysql_real_escape_string()

Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote '.

Lets try out this function on our two previous injection attacks and see how it works.

//NOTE: you must be connected to the database to use this function! // connect to MySQL $name_bad = "' OR 1'"; $name_bad = mysql_real_escape_string($name_bad); $query_bad = "SELECT * FROM customers WHERE username = '$name_bad'"; echo "Escaped Bad Injection: 
" . $query_bad . "
"; $name_evil = "'; DELETE FROM customers WHERE 1 or username = '"; $name_evil = mysql_real_escape_string($name_evil); $query_evil = "SELECT * FROM customers WHERE username = '$name_evil'"; echo "Escaped Evil Injection: 
" . $query_evil;

Display:

Notice that those evil quotes have been escaped with a backslash , preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:

• Bad: ' OR 1'
• Evil: '; DELETE FROM customers WHERE 1 or username = '

And I don't think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!

如何防止SQL注入

　　归纳一下，主要有以下几点：

　　1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。

　　2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

　　3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

　　4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

　　5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

　　6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具。

防止注入的几种办法

其实原来就是我们需要过滤一些我们常见的关键字和符合如：

Select，insert，update，delete，and，*，等等

function inject_check($sql_str) {

   returneregi('select|insert|update|delete|'|/*|*|../|./|union|into|load_file

|outfile', $sql_str);     // 进行过滤

}

或者是通过系统函数间的过滤特殊符号

Addslashes（需要被过滤的内容）

PHP其他地方安全设置

1、register_globals = Off   设置为关闭状态
2、SQL语句书写时尽量不要省略小引号和单引号

Select * FromTable Where id=2       (不规范)

Select * From·Table· Where `id`=’2’       (规范)

3、正确的使用 $_POST $_GET $_SESSION 等接受参数，并加以过滤

4、提高数据库命名技巧，对于一些重要的字段可根据程序特点命名

5、对于常用方法加以封装，避免直接暴露SQL语句

<?php function inject_check($sql_str) { $check=eregi('select|insert|update|delete|'|/*|*|../|./|union|into|load_file |outfile', $sql_str); // 进行过滤 if($check) { echo "输入非法内容！！"; exit(); } else return $sql_str; } $_GET['id']=inject_check($_GET['id']); $sql="select * from `table` where `id`='".$_GET['id']."'"; echo $sql; //下面是过滤单引号的 $_GET['id']=addslashes($_GET['id']);//addslashes的应用 $sql="select * from `table` where `id`='".$_GET['id']."'"; echo $sql; ?>

 SQL Injection 原理：
 结构化查询语言(SQL)是一种用来和数据库交互的文本语言。SQL Injection 就是利 用某些数据库的外部接口把用户数据插入到实际的数据库操作语言(SQL)当中，从而达到入侵数据库乃至操作系统的目的。它的产生主要是由于程序对用户输入 的数据没有进行严格的过滤，导致非法数据库查询语句的执行。
 
 如下面的用户登陆验证程序：
     $sql = "SELECT * FROM user WHERE username='$username' AND password='$password'";
     $result = mysql_db_query($dbname, $sql);
 如果我们提交如下url：
     http://127.0.0.1/injection/user.php?username=angel' or '1=1
 那么就可以成功登陆系统，但是很显然这并不是我们预期的，同样我们也可以利用sql的注释语句实现sql注入，如下面的例子：
     http://127.0.0.1/injection/user.php?username=angel'/*
     http://127.0.0.1/injection/user.php?username=angel'#
 这样就把后面的语句给注释掉了！说说这两种提交的不同之处，我们提交的第一句是利用逻辑运算，第二、三句是根据mysql的特性，mysql支持/*和#两种注释格式，所以我们提交的时候是把后面的代码注释掉，值得注意的是由于编码问题，在IE地址栏里提交#会变成空的，所以我们在地址栏提交的时候，应该提交#，才会变成#，就成功注释了， 这个比逻辑运算简单得多了。
 

二、开发中可以采取的措施

1、prepareStatement + Bind-variable
 对Java ,Jsp 开发的应用,可以使用  prepareStatement + Bind-variable 来防止sql注入,另外从PHP 5开始，也在扩展的mysqli中支持prepared statements，所以在使用这类语言作数据库开发时,强烈建议使用prepareStatement + Bind-variable 来实现,而尽量不要使用拼接的sql。
 
 2、使用应用程序提供的转换函数：
 很多应用程序接口都提供了对特殊的字符进行转换的函数，恰当的使用这些函数，可以防止应用程序用户输入使应用程序生成不期望的效果的语句的数值。
     MySQL C API：使用mysql_real_escape_string() API调用。
     MySQL++    ：使用escape和quote 修饰符。
     PHP        ：使用mysql_real_escape_string()函数（适用于PHP 4.3.0，之前的版本请使用mysql_escape_string(), PHP 4.0.3之前的版本请使用addslashes())。从PHP 5开始,可以使用扩展的mysqli，这是对MYSQL新特性的一个扩展支持，其中的一个优点就是支持prepared statements。
     Perl DBI    ：使用placeholders或者quote()方法。
     Ruby DBI    ：使用placeholders或者quote()方法。
     Java JDBC   ：使用PreparedStatement和 placeholders。
 
 3、自己定义函数进行校验
 如果现有的转换函数仍然不能满足要求，则需要自己编写函数进行输入校验。输入验证是一个很复杂的问题。输入验证的途径可以分为以下几种：
 ①整理数据使之变得有效；
 ②拒绝已知的非法输入；
 ③只接受已知的合法的输入。
 
 所以如果想要获得最好的安全状态，目前最好的解决办法就是对用户提交或者可能改变的数据进行简单分类，分别应用正则表达式来对用户提供的输入数据进行严格的检测和验证。