Classic case analysis of php sql injection and anti-injection

SQL injection and injection prevention are actually a combination of attack and defense. Today we are going to tell you the most basic injection and prevention methods. The principles are all caused by using some features of PHP or MySQL without us paying attention.

A simple SQL injection attack case
If we have a company website, all customer data and other important information are stored in the website's backend database. If there is such a command in the code of the website login page to read user information.

The code is as follows

Copy code

代码如下

复制代码

$q = "SELECT `id` FROM `users` WHERE `username`= ' " .$_GET['username']. " ' AND `password`= ' " .$_GET['password']. " ' ";

代码如下	复制代码
' ; SHOW TABLES;

代码如下	复制代码
'; DROP TABLE [table name];

Now there is a hacker who wants to attack your database. He will try to enter the following code in the username input box on this login page:

The code is as follows	Copy code
' ; SHOW TABLES;

Click the login button, and this page will display all tables in the database. If he now uses the following line of command:

The code is as follows	Copy code
'; DROP TABLE [table name];

In this way, he deleted a table!

代码如下	复制代码
INSERT INTO tbl_user SET uid="1"; SELECT * FROM tbl_user WHERE uid="1";

Of course, this is just a very simple example. The actual SQL injection method is much more complicated than this, and hackers are willing to spend a lot of time trying to attack your code. There are some software programs that can automatically continuously try SQL injection attacks. After understanding the principle of SQL injection attacks, let’s take a look at how to prevent SQL injection attacks.

Injection attack when magic_quotes_gpc = On

代码如下	复制代码
INSERT INTO tbl_user SET uid=1; SELECT * FROM tbl_user WHERE uid=1;

When magic_quotes_gpc = On, attackers cannot perform SQL injection on character fields. That doesn't mean it's safe. At this time, SQL injection can be performed through numeric fields. In the latest version of MYSQL 5.x, the input of data types has been stricted, and automatic type conversion has been turned off by default. Numeric fields cannot be character types marked with quotation marks. In other words, assuming uid is numeric, in previous mysql versions, such a statement is legal:

The code is as follows	Copy code
INSERT INTO tbl_user SET uid="1"; SELECT * FROM tbl_user WHERE uid="1";

In the latest MYSQL 5.x, the above statement is not legal and must be written like this:

The code is as follows	Copy code
INSERT INTO tbl_user SET uid=1; SELECT * FROM tbl_user WHERE uid=1;

I think this is correct. Because as a developer, submitting correct data types that comply with the rules to the database is the most basic requirement.

So how do attackers attack when magic_quotes_gpc = On? It's very simple, just perform SQL injection on numeric fields. Take the following php script as an example:

The code is as follows

Copy code

代码如下

复制代码

if ( isset($_POST["f_login"] ) )
{
// 连接数据库...
// ...代码略...

// 检查用户是否存在
$t_strUid = $_POST["f_uid"];
$t_strPwd = $_POST["f_pwd"];
$t_strSQL = "SELECT * FROM tbl_users WHERE uid=$t_strUid AND password = '$t_strPwd' LIMIT 0,1";
if ( $t_hRes = mysql_query($t_strSQL) )
{
// 成功查询之后的处理. 略...
}

}
?>
sample test

if ( isset($_POST["f_login"] ) )
{

// Connect to database...

代码如下	复制代码
SELECT * FROM tbl_users WHERE userid=1001 AND password = 'abc123' LIMIT 0,1

// ...The code is abbreviated...

// Check if the user exists

代码如下	复制代码
SELECT * FROM tbl_users WHERE userid=1001 OR 1 =1 # AND password = 'abc123' LIMIT 0,1

$t_strUid = $_POST["f_uid"];

$t_strPwd = $_POST["f_pwd"];

$t_strSQL = "SELECT * FROM tbl_users WHERE uid=$t_strUid AND password = '$t_strPwd' LIMIT 0,1";

if ( $t_hRes = mysql_query($t_strSQL) )

{

// Processing after successful query. Omitted...

}

代码如下

复制代码

$q = "SELECT `id` FROM `users` WHERE `username`= ' " .mysql_real_escape_string( $_GET['username'] ). " ' AND `password`= ' " .mysql_real_escape_string( $_GET['password'] ). " ' ";

?> sample test

The above script requires the user to enter userid and password to log in. A normal statement, the user inputs 1001 and abc123, and the submitted sql statement is as follows:

The code is as follows	Copy code
SELECT * FROM tbl_users WHERE userid=1001 AND password = 'abc123' LIMIT 0,1

If the attacker enters: 1001 OR 1 =1 # at userid, the injected sql statement is as follows:

The code is as follows	Copy code
SELECT * FROM tbl_users WHERE userid=1001 OR 1 =1 # AND password = 'abc123' LIMIT 0,1

The attacker achieved his goal. Prevent SQL injection - use the mysql_real_escape_string() function Use this function mysql_real_escape_string() in the database operation code to filter out special characters in the code, such as quotation marks. For example:

The code is as follows	Copy code
<🎜>$q = "SELECT `id` FROM `users` WHERE `username`= ' " .mysql_real_escape_string( $_GET['username'] ). " ' AND `password`= ' " .mysql_real_escape_string( $_GET[' password'] ). " ' ";<🎜> <🎜>?>

Prevent SQL injection - use mysql_query() function

The special thing about mysql_query() is that it will only execute the first line of SQL code, and the subsequent ones will not be executed. Recall that in the previous example, the hacker executed multiple SQL commands in the background through code and displayed the names of all tables. Therefore, the mysql_query() function can achieve further protection. We further evolve the code just now and get the following code:

In addition, we can also determine the length of the input value in the PHP code, or use a function to check the input value. Therefore, where user input values are accepted, input content must be filtered and checked. Of course, it is also very important to learn and understand the latest SQL injection methods so that purposeful prevention can be achieved. If you are using a platform-based website system such as WordPress, be sure to apply official patches or upgrade to a new version in a timely manner. If there is something wrong or you don’t understand, please leave a message in the comment area.

The code is as follows

代码如下

复制代码

//connection
$database = mysql_connect("localhost", "username","password");

//db selection
mysql_select_db("database", $database);

$q = mysql_query("SELECT `id` FROM `users` WHERE `username`= ' " .mysql_real_escape_string( $_GET['username'] ). " ' AND `password`= ' " .mysql_real_escape_string( $_GET['password'] ). " ' ", $database);

Copy code

//connection

代码如下	复制代码
$t_strSQL = "SELECT a from b...."; if ( mysql_query($t_strSQL) ) { // 正确的处理 } else { echo "错误! SQL 语句：$t_strSQL rn错误信息".mysql_query(); exit; }

$database = mysql_connect("localhost", "username","password");

//db selection

mysql_select_db("database", $database);

代码如下	复制代码
define("DEBUG_MODE",0); // 1: DEBUG MODE; 0: RELEASE MODE

代码如下	复制代码
$t_strSQL = "SELECT a from b...."; if ( mysql_query($t_strSQL) ) { // 正确的处理 } else { if (DEBUG_MODE) echo "错误! SQL 语句：$t_strSQL rn错误信息".mysql_query(); exit; }

The display_errors option in php.ini should be set to display_errors = off. In this way, after an error occurs in the php script, the error will not be output on the web page to prevent attackers from analyzing useful information. When calling mysql functions such as mysql_query, @ should be added in front, that is, @mysql_query(...), so that mysql errors will not be output. The same is true to prevent attackers from analyzing useful information. In addition, some programmers are used to outputting errors and sql statements when mysql_query errors when developing, such as:

The code is as follows

Copy code

$t_strSQL = "SELECT a from b.. .."; if ( mysql_query($t_strSQL) ) { // Correct processing } else { echo "Error! SQL statement: $t_strSQL rn error message".mysql_query(); exit; } This approach is quite dangerous and stupid. If you must do this, it is best to set a global variable or define a macro in the website configuration file and set the debug flag: In global configuration file: //In calling script:

The code is as follows

Copy code

$t_strSQL = "SELECT a from b.. .."; if ( mysql_query($t_strSQL) ) { // Correct processing } else { if (DEBUG_MODE) echo "Error! SQL statement: $t_strSQL rn error message".mysql_query(); exit; } About sql anti-injection content http://www.bKjia.c0m/phper/phpanqn/37704.htm

Related labels：

php sql and us attack case analysis injection of classic want

source：php.cn

Previous article：Solution to sending a large number of data packets externally to PHP_PHP Tutorial Next article：A comprehensive PHP website anti-injection program_PHP tutorial

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn