The technical implementation of PHP+SQL injection attack and its prevention
- 2020-03-31 21:22:15
- OfStack
Sum up the experience. In my opinion, the main reasons for SQL injection attacks are as follows:
The magic_quotes_gpc option in the PHP configuration file php.ini is not on and is set to off
2. Developers fail to check and escape data types
But in fact, the second point is the most important. In my opinion, checking the data type entered by the user and submitting the correct data type to MYSQL should be the most basic quality of a web programmer. In reality, however, many white-haired Web developers often forget this, leaving the back door wide open.
Why is the second point the most important? Because without the assurance of the second point, the magic_quotes_gpc option, whether on or off, could trigger an SQL injection attack. Here's a look at the technical implementation:
I. injection attack when magic_quotes_gpc = Off
Magic_quotes_gpc = Off is a very insecure option in PHP. The new version of PHP has changed the default value to On. But there are still quite a few servers with the option off. After all, antique servers are also used by people.
When magic_quotes_gpc = On, it will automatically prefix all the '(single quote), "(double sign), \(backslash), and white space characters in the submitted variable. Here's the official PHP description:
If there is no escape, i.e., off, it gives the attacker an opportunity. Take the following test script:
In this script, when the user enters the normal username and password, assuming the values are zhang3 and abc123, the SQL statements submitted are as follows:
If the attacker enters zhang3' OR 1=1 # in the username field and enters abc123 in the password, the SQL statement submitted becomes as follows:
Since # is an annotation in mysql, the statement after # is not executed, so this line of statement is:
This allows the attacker to bypass the authentication. It is even more dangerous if the attacker knows the database structure and it builds a UNION SELECT:
Suppose you type in username: zhang3 'OR 1 =1 UNION select cola, colb,cold FROM tbl_b #
In password: abc123,
The submitted SQL statement becomes:
OR 1 =1 UNION select cola, colb,cold FROM tbl_b #' AND password = 'abc123' LIMIT 0,1
That's pretty dangerous. If the agic_quotes_gpc option is on, and the quotation marks are escaped, the attack statement constructed by the attacker above will look like this, failing to achieve its purpose:
Injection attack when magic_quotes_gpc = On
When magic_quotes_gpc = On, the attacker cannot inject SQL into the character fields. That doesn't mean it's safe. At this point, you can perform SQL injection through numeric fields.
In the latest version of MYSQL 5.x, data type input has been strictly enforced and automatic type conversion has been turned off by default. Numeric field, cannot be the character type of quotation mark. That is, assuming that uid is numeric, such statements were legal in previous versions of mysql:
In the latest MYSQL 5.x, the above statement is not valid and must be written like this:
I think that's right. Because as a developer, submitting the correct data type to the database that conforms to the rules is a basic requirement.
So how do attackers attack when magic_quotes_gpc = On? It is as simple as SQL injection for numeric fields. Take the following PHP script:
The script above asks the user to log in with a userid and password. A normal statement, the user enters 1001 and abc123, the SQL statement submitted is as follows:
SELECT * FROM tbl_users WHERE userid=1001 AND password = 'abc123' LIMIT 0,1
If the attacker at userid enters: 1001 OR 1 =1 #, the injected SQL statement is as follows:
SELECT * FROM tbl_users WHERE userid=1001 OR 1 =1 # AND password = 'abc123' LIMIT 0,1
The attackers got their way.
How to prevent PHP SQL injection attack
How to prevent PHP SQL injection attacks? I think 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. This way, when a PHP script goes wrong, it doesn't print an error on the web page, allowing the attacker to analyze the information.
When calling mysql functions such as mysql_query, it should be preceded by @, namely @mysql_query(...). , so that mysql errors will not be output. Do the same to prevent the attacker from analyzing useful information. In addition, some programmers are used to output errors and SQL statements when mysql_query error occurs, such as:
This is dangerous and foolish. If you must, set a global variable or define a macro in your site's configuration file and set the debug flag:
In the global configuration file:
Escape and type check for submitted SQL statements.
Four. I wrote a security parameter acquisition function
In order to prevent user error data and PHP + mysql injection, I wrote a function PAPI_GetSafeParam() to obtain the safe parameter value:
In this function, there are three arguments:
$pi_strName: variable name
$pi_Def: the default value
$pi_iType: data type. Values XH_PARAM_INT, XH_PARAM_TXT, respectively, for numeric and textual.
If the request is numeric, call is_numeric() to determine whether it is numeric. If not, the default value specified by the program is returned.
For simplicity, I escaped all the dangerous characters (including the HTML code) that the user entered for the text string. Because of a bug in the PHP function addslashes(), I replaced it directly with str_replace(). The get_magic_quotes_gpc() function is a PHP function that determines whether the magic_quotes_gpc option is turned on.
In the example in the second section, the code can call:
In that case, it's pretty safe. PAPI_GetSafeParam's code is a bit long, but it's worth sacrificing that efficiency for security. I hope you will give me more criticism. :)
The magic_quotes_gpc option in the PHP configuration file php.ini is not on and is set to off
2. Developers fail to check and escape data types
But in fact, the second point is the most important. In my opinion, checking the data type entered by the user and submitting the correct data type to MYSQL should be the most basic quality of a web programmer. In reality, however, many white-haired Web developers often forget this, leaving the back door wide open.
Why is the second point the most important? Because without the assurance of the second point, the magic_quotes_gpc option, whether on or off, could trigger an SQL injection attack. Here's a look at the technical implementation:
I. injection attack when magic_quotes_gpc = Off
Magic_quotes_gpc = Off is a very insecure option in PHP. The new version of PHP has changed the default value to On. But there are still quite a few servers with the option off. After all, antique servers are also used by people.
When magic_quotes_gpc = On, it will automatically prefix all the '(single quote), "(double sign), \(backslash), and white space characters in the submitted variable. Here's the official PHP description:
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, i.e., off, it gives the attacker an opportunity. Take the following test script:
<?
if ( isset($_POST["f_login"] ) )
{
//Connect to database...
//. The code is slightly...
//Check if 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 0,1";
if ( $t_hRes = mysql_query($t_strSQL) )
{
//Processing after a successful query.
}
}
?>
<html><head><title>sample test</title></head>
<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=" The login ">
</form>
</body>
In this script, when the user enters the normal username and password, assuming the values are zhang3 and abc123, the SQL statements submitted are as follows:
SELECT * FROM tbl_users
WHERE username='zhang3' AND password = 'abc123' LIMIT 0,1
If the attacker enters zhang3' OR 1=1 # in the username field and enters abc123 in the password, the SQL statement submitted becomes as follows:
SELECT * FROM tbl_users
WHERE username='zhang3' OR 1=1 #' AND password = 'abc123' LIMIT 0,1
Since # is an annotation in mysql, the statement after # is not executed, so this line of statement is:
SELECT * FROM tbl_users
WHERE username='zhang3' OR 1=1
This allows the attacker to bypass the authentication. It is even more dangerous if the attacker knows the database structure and it builds a UNION SELECT:
Suppose you type in username: zhang3 'OR 1 =1 UNION select cola, colb,cold FROM tbl_b #
In password: abc123,
The submitted SQL statement becomes:
SELECT * FROM tbl_users
WHERE username='zhang3 '
OR 1 =1 UNION select cola, colb,cold FROM tbl_b #' AND password = 'abc123' LIMIT 0,1
That's pretty dangerous. If the agic_quotes_gpc option is on, and the quotation marks are escaped, the attack statement constructed by the attacker above will look like this, failing to achieve its purpose:
SELECT * FROM tbl_users
WHERE username='zhang3' 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
Injection attack when magic_quotes_gpc = On
When magic_quotes_gpc = On, the attacker cannot inject SQL into the character fields. That doesn't mean it's safe. At this point, you can perform SQL injection through numeric fields.
In the latest version of MYSQL 5.x, data type input has been strictly enforced and automatic type conversion has been turned off by default. Numeric field, cannot be the character type of quotation mark. That is, assuming that uid is numeric, such statements were legal in previous versions of mysql:
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 valid and must be written like this:
INSERT INTO tbl_user SET uid=1;
SELECT * FROM tbl_user WHERE uid=1;
I think that's right. Because as a developer, submitting the correct data type to the database that conforms to the rules is a basic requirement.
So how do attackers attack when magic_quotes_gpc = On? It is as simple as SQL injection for numeric fields. Take the following PHP script:
<?
if ( isset($_POST["f_login"] ) )
{
//Connect to database...
//. The code is slightly...
//Check if 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 a successful query.
}
}
?>
<html><head><title>sample test</title></head>
<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=" The login ">
</form>
</body>
The script above asks the user to log in with a userid and password. A normal statement, the user enters 1001 and abc123, the SQL statement submitted is as follows:
SELECT * FROM tbl_users WHERE userid=1001 AND password = 'abc123' LIMIT 0,1
If the attacker at userid enters: 1001 OR 1 =1 #, the injected SQL statement is as follows:
SELECT * FROM tbl_users WHERE userid=1001 OR 1 =1 # AND password = 'abc123' LIMIT 0,1
The attackers got their way.
How to prevent PHP SQL injection attack
How to prevent PHP SQL injection attacks? I think 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. This way, when a PHP script goes wrong, it doesn't print an error on the web page, allowing the attacker to analyze the information.
When calling mysql functions such as mysql_query, it should be preceded by @, namely @mysql_query(...). , so that mysql errors will not be output. Do the same to prevent the attacker from analyzing useful information. In addition, some programmers are used to output errors and SQL statements when mysql_query error occurs, such as:
$t_strSQL = "SELECT a from b....";
if ( mysql_query($t_strSQL) )
{
//Proper handling
}
else
{
echo " error ! SQL Statement: $t_strSQL rn The error message ".mysql_query();
exit;
}
This is dangerous and foolish. If you must, set a global variable or define a macro in your site's configuration file and set the debug flag:
In the global configuration file:
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) )
{
//Proper handling
}
else
{
if (DEBUG_MODE)
echo " error ! SQL Statement: $t_strSQL rn The error message ".mysql_query();
exit;
}
Escape and type check for submitted SQL statements.
Four. I wrote a security parameter acquisition function
In order to prevent user error data and PHP + mysql injection, I wrote a function PAPI_GetSafeParam() to obtain the safe parameter value:
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 arguments:
$pi_strName: variable name
$pi_Def: the default value
$pi_iType: data type. Values XH_PARAM_INT, XH_PARAM_TXT, respectively, for numeric and textual.
If the request is numeric, call is_numeric() to determine whether it is numeric. If not, the default value specified by the program is returned.
For simplicity, I escaped all the dangerous characters (including the HTML code) that the user entered for the text string. Because of a bug in the PHP function addslashes(), I replaced it directly with str_replace(). The get_magic_quotes_gpc() function is a PHP function that determines whether the magic_quotes_gpc option is turned on.
In the example in the second section, the code can call:
<?
if ( isset($_POST["f_login"] ) )
{
//Connect to database...
//. The code is slightly...
//Check if 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 a successful query.
}
}
?>
In that case, it's pretty safe. PAPI_GetSafeParam's code is a bit long, but it's worth sacrificing that efficiency for security. I hope you will give me more criticism. :)