PHP + SQL injection technology implementation and Prevention Measures

Source: Internet
Author: User
Tags mysql functions mysql injection

Summarize the experience. In my opinion, the main cause of SQL injection attacks is the following two reasons:
1. The magic_quotes_gpc option in the php configuration file php. ini is disabled.
2. The developer does not check and escape the data type.
But in fact, the second point is the most important. In my opinion, it should be the most basic quality for web programmers to check the data types entered by users and submit the correct data types to MYSQL. But in reality, many small Web developers often forget this, leading to a wide open backdoor.
Why is the second most important? Without the second guarantee, the magic_quotes_gpc option may cause SQL injection attacks, whether on or off. The following describes the technical implementation:
1. injection attack when magic_quotes_gpc = Off
Magic_quotes_gpc = Off is a very insecure option in php. The new php version has changed the default value to On. However, there are still a considerable number of server options that are off. After all, some old servers are also used.
When magic_quotes_gpc = On, it will include all '(single quotes), "(double signs), \ (backslash), and white space characters in the submitted variables, \ is automatically added to the front \. The official instructions for php are as follows:

Copy codeThe Code is as follows: magic_quotes_gpc boolean

Sets the magic_quotes state for GPC (Get/Post/Cookie) operations. when magic_quotes are on, all' (single-quote), "(double quote), \ (backslash) and NUL's are escaped with a backslash automatically

If there is no escape, that is, off, attackers can take advantage of it. The following test script is used as an example:Copy codeThe Code is as follows: <?
If (isset ($ _ POST ["f_login"])
{
// Connect to the database...
//... Code omitted...

// Check whether the user exists
$ T_strUname = $ _ POST ["f_uname"];
$ T_strPwd = $ _ POST ["f_pwd"];
$ T_strSQL = "SELECT * FROM tbl_users WHERE username = '$ t_strUname' AND password = '$ t_strPwd' LIMIT ";

If ($ t_hRes = mysql_query ($ t_strSQL ))
{
// Processing after successful query...
}
}
?>

<Html> <Body>
<Form method = post action = "">
Username: <input type = "text" name = "f_uname" size = 30> <br>
Password: <input type = text name = "f_pwd" size = 30> <br>

<Input type = "submit" name = "f_login" value = "Logon">
</Form>
</Body>

In this script, when the user enters the normal user name and password, assuming the values are zhang3 and abc123, the SQL statement submitted is as follows:Copy codeThe Code is as follows: SELECT * FROM tbl_users
WHERE username = 'hangzhou3' AND password = 'abc123' LIMIT

If the attacker enters zhang3 'OR 1 = 1 # In the username field and abc123 in the password field, the SQL statement submitted is changed to the following:Copy codeThe Code is as follows: SELECT * FROM tbl_users
WHERE username = 'hangzhou' OR 1 = 1 # 'AND password = 'abc123' LIMIT

Because # Is a comment character in mysql, # the subsequent statement is not executed, and the above statement is implemented:Copy codeThe Code is as follows: SELECT * FROM tbl_users
WHERE username = 'hangzhou3' OR 1 = 1

In this way, attackers can bypass authentication. If attackers know the database structure, it is more dangerous to construct a union select statement:

Assume that in username, enter: zhang3 'OR 1 = 1 UNION select cola, colb, cold FROM tbl_ B #

Enter abc123 in password,

The submitted SQL statement is changed:Copy codeThe Code is as follows: SELECT * FROM tbl_users
WHERE username = 'hangzhou3'

OR 1 = 1 UNION select cola, colb, cold FROM tbl_ B # 'AND password = 'abc123' LIMIT 0, 1
This is quite dangerous. If the agic_quotes_gpc option is on and the quotation marks are escaped, the attack statement constructed by the above attacker will become like this and thus cannot achieve its purpose:Copy codeThe Code is as follows: SELECT * FROM tbl_users
WHERE username = 'hangzhou3 \ 'OR 1 = 1 #'
AND password = 'abc123'
LIMIT 0, 1

SELECT * FROM tbl_users
WHERE username = 'zhang3 \ 'OR 1 = 1 UNION select cola, colb, cold FROM tbl_ B #'
AND password = 'abc123' LIMIT 0, 1

2. injection attack when magic_quotes_gpc = On
When magic_quotes_gpc = On, attackers cannot perform SQL Injection On fields of the stable type. This does not mean this is safe. In this case, you can use numeric fields for SQL injection.

In MYSQL 5.x of the latest version, data type input is strictly specified, and automatic type conversion is disabled by default. A numeric field cannot be a quote type marked with quotation marks. That is to say, if the uid is numeric, in the previous mysql version, such a statement is legal:Copy codeThe Code is as follows: insert into tbl_user SET uid = "1 ";
SELECT * FROM tbl_user WHERE uid = "1 ";

In the latest MYSQL 5.x, the preceding statement is not legal and must be written as follows:Copy codeThe Code is as follows: insert into tbl_user SET uid = 1;
SELECT * FROM tbl_user WHERE uid = 1;

In this case, I think it is correct. As a developer, it is the most basic requirement to submit correct data types that comply with the Rules to the database.

How can attackers attack magic_quotes_gpc = On? It is very simple, that is, SQL injection to numeric fields. The following php script is used as an example:Copy codeThe Code is as follows: <?
If (isset ($ _ POST ["f_login"])
{
// Connect to the database...
//... Code omitted...

// Check whether the user exists
$ 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...
}

}
?>
<Html> <Body>
<Form method = post action = "">
User ID: <input type = "text" name = "f_uid" size = 30> <br>

Password: <input type = text name = "f_pwd" size = 30> <br>
<Input type = "submit" name = "f_login" value = "Logon">
</Form>
</Body>

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

SELECT * FROM tbl_users WHERE userid = 1001 AND password = 'abc123' LIMIT
If the attacker inputs 1001 OR 1 = 1 # At userid, the SQL statement injected is as follows:

SELECT * FROM tbl_users WHERE userid = 1001 OR 1 = 1 # AND password = 'abc123' LIMIT
The attacker achieved the goal.

Iii. How to Prevent php SQL injection attacks
How to Prevent php SQL injection attacks? In my opinion, the most important thing is to check and escape data types. The following rules are summarized:

The display_errors option in php. ini should be set to display_errors = off. In this way, php scripts do not output errors on web pages, so that attackers can analyze the information.
When calling mysql functions such as mysql_query, add @, I .e. @ mysql_query (...), so that mysql errors will not be output. Similarly, attackers may not analyze useful information. In addition, some programmers are used to output errors and SQL statements when mysql_query errors during development, such:Copy codeThe Code is as follows: $ t_strSQL = "SELECT a from B ....";
If (mysql_query ($ t_strSQL ))
{
// Correct Handling
}
Else
{
Echo "error! SQL statement: $ t_strSQL \ r \ n error message ". mysql_query ();
Exit;
}

This approach is quite dangerous and stupid. To do this, you 'd better set a global variable or define a macro in the website configuration file and set the debug flag:

In the global configuration file:Copy codeCode: define ("DEBUG_MODE", 0); // 1: debug mode; 0: RELEASE MODE

// In the call script:
$ T_strSQL = "SELECT a from B ....";
If (mysql_query ($ t_strSQL ))
{
// Correct Handling
}
Else
{
If (DEBUG_MODE)
Echo "error! SQL statement: $ t_strSQL \ r \ n error message ". mysql_query ();
Exit;
}

Run the convert and type checks on the submitted SQL statements.
4. I wrote a security parameter to obtain the function.
To prevent incorrect data and php + mysql injection, I wrote a function PAPI_GetSafeParam () to obtain the safe parameter values:Copy codeThe Code is as follows: define ("XH_PARAM_INT", 0 );
Define ("XH_PARAM_TXT", 1 );
Function PAPI_GetSafeParam ($ pi_strName, $ pi_Def = "", $ pi_iType = XH_PARAM_TXT)
{
If (isset ($ _ GET [$ pi_strName])
$ T_Val = trim ($ _ GET [$ pi_strName]);
Else if (isset ($ _ POST [$ pi_strName])
$ T_Val = trim ($ _ POST [$ pi_strName]);
Else
Return $ pi_Def;

// INT
If (XH_PARAM_INT = $ pi_iType)
{
If (is_numeric ($ t_Val ))
Return $ t_Val;
Else
Return $ pi_Def;
}

// String
$ T_Val = str_replace ("&", "&", $ t_Val );
$ T_Val = str_replace ("<", "<", $ t_Val );
$ T_Val = str_replace (">", ">", $ t_Val );
If (get_magic_quotes_gpc ())
{
$ T_Val = str_replace ("\\\" "," ", $ t_Val );
$ T_Val = str_replace ("\'' "," '", $ t_Val );
}
Else
{
$ T_Val = str_replace ("\" "," ", $ t_Val );
$ T_Val = str_replace ("'", "'", $ t_Val );
}
Return $ t_Val;
}

In this function, there are three parameters:

$ Pi_strName: variable name
$ Pi_Def: Default Value
$ Pi_iType: data type. The optional values are XH_PARAM_INT and XH_PARAM_TXT, which indicate the numeric type and the text type respectively.
If the request is numeric, call is_numeric () to determine whether the request is Numeric. If not, return the default value specified by the program.

For simplicity, for text strings, I escape all the dangerous characters (including HTML code) entered by the user. Due to the vulnerability in php function addslashes (), I replaced it directly with str_replace. The get_magic_quotes_gpc () function is a php function used to determine whether the magic_quotes_gpc option is enabled.

The sample code in Section 2 can be called as follows:Copy codeThe Code is as follows: <?
If (isset ($ _ POST ["f_login"])
{
// Connect to the database...
//... Code omitted...

// Check whether the user exists
$ T_strUid = PAPI_GetSafeParam ("f_uid", 0, XH_PARAM_INT );
$ T_strPwd = PAPI_GetSafeParam ("f_pwd", "", XH_PARAM_TXT );
$ 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...
}
}
?>

In this way, it is quite safe. PAPI_GetSafeParam code is a little long, but at the cost of efficiency, it is worthwhile to ensure security. I hope you will give more criticism. :)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.