How to defend against SQL injection

For server configuration level protection, should ensure that the production environment webserver is to turn off the error message, such as PHP in the production environment configuration file php.ini Display_errors should be set to off, so that the error is turned off, Let's look at how to prevent SQL injection from a coding perspective.

The above uses two examples to analyze the SQL injection attack skill, can see, any SQL Injection vulnerability program, because the program to accept from the client user input variables or URL passed parameters, and this variable or parameter is part of the SQL statement, for the user input or passed parameters, We should always be vigilant, this is the security field of "external data is not trusted" principle, throughout the Web security area of the various attacks, most of all because the developers violated this principle caused, so naturally can think of is from the variable detection, filtering, verification, Make sure the variables are expected by the developer.

1. Check the variable data type and format

If your SQL statement is similar to where id={$id} This form, all the IDs in the database are numbers, then you should check to make sure that the variable ID is int type before SQL is executed, and if you accept the mailbox, you should check and make sure that the variable must be the format of the mailbox. Other types, such as date and time, are also a reason. Summing up: as long as there are fixed-format variables, before the SQL statement execution, you should strictly follow the fixed format to check, to ensure that the variables are the format we expected , which can largely avoid SQL injection attacks.

For example, we accept the username parameter example, our product design should be at the beginning of user registration, there is a user name rules, such as 5-20 characters, can only be composed of uppercase and lowercase letters, numbers and some security symbols, does not contain special characters. At this point we should have a check_username function to perform a uniform check. However, there are still many exceptions that cannot be applied to this criterion, such as the article publishing system, commenting systems, etc., which must allow the user to submit arbitrary strings, which requires filtering and other scenarios.

2. Filter Special Symbols

For variables that cannot be determined in a fixed format, special symbolic filtering or escaping is a must. In PHP, for example, the Addslashes function is usually used, which adds a backslash escape before the specified predefined character, which is the single quotation mark (') Double quotation mark (') backslash (\) NULL.

Look at 2 SQL statements:

$uid = isset($_GET[‘uid‘]) ? $_GET[‘uid‘] : 0;$uid = addslashes(uid);$sql = "SELECT uid,username FROM user WHERE uid=‘{$uid}‘";

And

$uid = isset($_GET[‘uid‘]) ? $_GET[‘uid‘] : 0;$uid = addslashes(uid);$sql = "SELECT uid,username FROM user WHERE uid={$uid}";

The above two query statements have passed the PHP addslashes function filter escape, but the security is very different, in MySQL, for the Int type field of the condition query, the query effect of the above statement is exactly the same, because the first sentence of SQL variable enclosed in quotation marks, In SQL injection, the first problem that hackers face is the need to close the preceding single quotation marks, so that the subsequent statements as SQL execution, and also to comment out the original SQL statement after the single quotation marks, so that can be successfully injected, because the code used the Addslashes function, The hacker's attack will not be possible, but the second sentence does not include the variable in quotation marks, the hacker does not have to consider to close, comment, so even if the same addslashes escaped, there is still a SQL attack vulnerability.

For PHP program +mysql Architecture Program, in the dynamic SQL statement, the use of single quotes to include variables with the Addslashes function is an effective way to deal with SQL injection attacks, but this is not enough, like the above 2 SQL statements, according to the " Check the data type "principle, UID should be the INTVAL function format int type, this can not only effectively avoid the second statement of SQL Injection vulnerability, but also can make the program look more natural, especially in nosql (such as MongoDB), the variable type must match the field type can be.

As can be seen from the above, the second SQL statement is vulnerable, but because of the use of the Addslashes function, you will find that the hacker's attack statement also has a condition that can not use special symbols, such as where username= ' Plhwin ' Such an attack statement is impossible to execute, But hackers can convert a string into 16-encoded data or use a char function for the same purpose. And because of the existence of SQL reserved keywords, such as "having", "order by", even if a black-and-white list-based filtering method is still more or less problematic, is there any other way to prevent SQL injection?

3. Binding variables, using precompiled statements

MySQL's mysqli driver provides support for precompiled statements, different programming languages, each with a method of using precompiled statements, we still use PHP, for example, to write userinfo2.php code:

<?phpHeader(' content-type:text/html; Charset=utf-8 ');$username = isset($_get[' username ']) ? $_get[' username '] : "';$userinfo = Array();if($username){//Using the Mysqli driver to connect to the demo database$mysqli = New mysqli("localhost", "Root", "Root", ' demo ');//Use question mark instead of variable position$sql = "Select Uid,username from User WHERE username=?";$stmt = $mysqli -Prepare($sql);//Bind variable$stmt -Bind_param("S", $username);$stmt -Execute();$stmt -Bind_result($uid, $username); while ($stmt -Fetch()) {    $row = Array();    $row[' uid '] = $uid;    $row[' username '] = $username;    $userinfo[] = $row;}}Echo ' <pre> ',Print_r($userinfo, 1),' </pre> ';

As you can see from the code above, the Addslashes function is not used in our program, but the browser runs http://localhost/test/userinfo2.php?username=plhwin ' and 1=1--hack without any results, the SQL vulnerability does not exist in this program.

In fact, binding variables using precompiled statements is the best way to prevent SQL injection , using precompiled SQL statement semantics will not change, in the SQL statement, the variable with a question mark? The hacker can not change the structure of the SQL statement even if it is big, like the example above, The plhwin ' and 1=1--hack parameters passed by the username variable are interpreted as a username string, which fundamentally eliminates the occurrence of SQL injection attacks.

Security of database information encryption

I believe that we are also the csdn of the 2011 burst of memories of the event, which led to CSDN in the forefront of the cause of the attack is that they actually stored the user's password, which led to the scientific and technological community on user information security, especially password security of strong concern, We are in the prevention of SQL injection at the same time, it should be a rainy day, maybe the next one is dragged the library is you, who knows.

In web development, the traditional encryption and decryption can be broadly divided into three kinds:

1. Symmetric encryption:

That is, both the encrypting and decrypting parties use the same encryption algorithm and key, the preservation of the key of this scheme is very critical, because the algorithm is public, and the key is confidential, once the key leaks, hackers can still easily decrypt. The common symmetric encryption algorithms are: AES, Des, and so on.

2. Asymmetric Encryption:

Even with different keys for encryption and decryption, the key is divided into public and private keys, the data encrypted with the private key must be decrypted with the public key, the same public key encrypted data must be decrypted with the corresponding private key, the common asymmetric Encryption algorithm is: RSA.

3, non-reversible encryption:

The hashing algorithm can not decrypt the original data after encrypting the data, so the hashing algorithm is commonly used: MD5, SHA-1 and so on.

In the sample code of the system above us, $MD 5password = MD5 ($password); From this code can see the use of MD5 's irreversible encryption algorithm to store passwords, which is the industry's common password encryption algorithm over the years, but this is still unsafe. Why is it?

This is because MD5 encryption has a feature: the same string is generated after the MD5 hash of the cryptographic string is also the same, because the industry has been using this encryption for a long while, hackers have prepared their own powerful MD5 rainbow table to reverse the string before the encryption, This is used for reverse anti-push MD5 encryption Rainbow table everywhere on the Internet, in Google using MD5 decryption as a keyword search, you can find MD5 online hack site, We insert the user data when the MD5 encryption string e10adc3949ba59abbe56e057f20f883e Fill in, instantly can be encrypted before the password: 123456. Of course not every one can succeed, but to be sure, this rainbow table will be more and more perfect.

Therefore, we have an urgent need to use a better method of password data irreversible encryption, the usual practice is to determine a different password for each user to add salt (salt), and then mix the user's real password for MD5 encryption, such as the following code:

<?php//password set when the user registers$password = $_post[' Password '];//MD5 Encryption, the traditional practice of storing encrypted strings directly into the database, but this is not enough, we continue to improve$PASSWORDMD 5 = MD5($password);//For users to generate a different password salt, the algorithm can be based on the needs of their business and different$salt = substr(uniqid(Rand()), -6);//New encrypted string contains the cipher salt$PASSWORDMD 5 = MD5($PASSWORDMD 5.$salt);

Summary

1, do not arbitrarily open the production environment webserver error display.
2, never trust the variable input from the user side, there are fixed-format variables must strictly check the corresponding format, no fixed-format variables need to quote special characters such as the necessary filtering escape.
3. Use a precompiled SQL statement that binds the variable.
4, do a good job of database account rights management.
5, strict encryption process the user's confidential information.

How to defend against SQL injection