How to prevent sql injection failure

How to prevent sql injection attack in php

PHP simple implementation to prevent sql injection

Method 1: execute substituting parameters

<?php

if(count($_POST)! =0){

$host=’aaa’;

$database=’bbb’;

$username=’ccc’;

$password=’***’;

$num=0;

$pdo=newPDO(” mysql:host=$host;dbname=$database”,$username,$password);//create a pdo object

foreach($_POSTas$var_Key=>$var_Value){

//get POST Maximum value of the array

$num=$num+1;

}

// The array with subscript i stores the id of the item, and the one with subscript j stores the inventory of the item

for($i=0;$i<$num;$i=$i+2)

{

/// Inventory Subscripts

$j=$i+1;

//Judging the legitimacy of the passed data

if(is_numeric(trim($_POST[$i])) &&is_numeric(trim($_POST[$j]))){

//disable the simulation effect of preparedstatements

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);

///Queries the database to see if an item with this ID exists

/// When calling prepare (), the query statement has already been sent to the database server, at which point only the placeholder ? sent over, there is no data submitted by the user

$stmt=$pdo->prepare(“selectgood_idfromdelphi_test_contentWHEREgood_id=?”) ;

// When execute() is called, the values submitted by the user are transmitted to the database, they are transmitted separately, both independently, and the SQL attacker doesn’t have the slightest chance.

$stmt->execute(array($_POST[$i]));

//Returns the query result

$count=$stmt->rowCount();

//If the local database exists for this item ID and inventory record, update the item’s Inventory

if($count!=0)

{

$stmt=$pdo->prepare(“updatedelphi_test_contentsetcontent=?WHEREgood_id=?”) ;

$stmt->execute(array($_POST[$j],$_POST[$i]));

}

/// If the local database doesn’t have a record for that item ID and inventory, add that record

if($count==0)

{

$stmt=$pdo->prepare(“insertintodelphi_test_content(good_id,content)values(? ,?)”) )

$stmt->execute(array($_POST[$i],$_POST[$j]));

}

}

}

$pdo=null;

/Close connection

}

? > Method 2: bindParam bind parameter

<?php

if(count($_POST)! =0){

$host=’aaa’;

$database=’bbb’;

$username=’ccc’;

$password=’***’;

$num=0;

$pdo=newPDO(” mysql:host=$host;dbname=$database”,$username,$password);//create a pdo object

foreach($_POSTas$var_Key=>$var_Value){

//get POST Maximum value of the array

$num=$num+1;

}

// The array with subscript i stores the id of the item, and the one with subscript j stores the inventory of the item

for($i=0;$i<$num;$i=$i+2)

{

/// Inventory Subscripts

$j=$i+1;

//Judging the legitimacy of the passed data (this data is the item number as well as the inventory, strictly speaking the string is all made up of numbers)

if(is_numeric(trim($_POST[$i]))&&is_numeric(trim( $_POST[$j]))){

//Query the database to see if the item with that ID exists

$stmt=$pdo->prepare(“selectgood_idfromdelphi_test_contentWHEREgood_id=?”) p>

$count=$stmt->rowCount();

//Update the item’s inventory if the local database has a record for that item’s ID and inventory

if($count!=0)

{

$stmt=$pdo->prepare(” updatedelphi_test_contentsetcontent=?WHEREgood_id=?”) ;

$stmt->execute(array($_POST[$j],$_POST[$i]));

$stmt->bindParam(1,$_POST[$j]);

$stmt->bindParam(2,$_ POST[$i]);

$stmt->execute();

}

///If there is no record for this item ID and inventory in the local database, add the record

if($count==0)

{

$stmt=$pdo-&gt ;prepare(“insertintodelphi_test_content(good_id,content)values(? ,?)”) ;

$stmt->bindParam(1,$_POST[$i]);

$stmt->bindParam(2,$_POST[$j]);

$stmt->execute();

}

}

}

$pdo=null;

//Close connection

}

? >

How to defend against SQL injection

$uid=addslashes(uid);

$sql=”SELECTuid,usernameFROMuserWHEREuid='{$uid} ‘”;

and

$uid=isset($_GET[‘uid’])? $_GET[‘uid’]:0;

$uid=addslashes(uid);

$sql=”SELECTuid,usernameFROMuserWHEREuid={$uid}”;

The above two query statements have been filtered and escaped by php’s adslashes function, but they are very different in terms of security. In MySQL, for conditional querying of int-type fields, the query effect of the above statements is exactly the same, and since the variables in the first SQL sentence are enclosed in single quotes, the first problem faced by hackers during SQL injection is that they have to close the single quotes in the front first so as to make it possible to inject into the SQL field. single quotes, so that the latter statement as SQL execution, and also to comment out the original SQL statement in the back of the single quotes, so that you can successfully inject, due to the use of the code in the adslashes function, the hacker’s attack will not be able to start, but the second sentence does not contain the variables in quotes, the hacker does not have to consider the closure, comment, so even if the same use of the addslashes escaping, there is still a SQL attack vulnerability.

For the PHP program + MySQL architecture program, in the dynamic SQL statement, the use of single quotes to contain the variable with the addslashes function is an effective means to cope with SQL injection attacks, but this is not enough to do, like the above 2 SQL statement, according to the “check data type” principle, uid should be through the intval function. Formatted as int, this not only effectively avoids the SQL injection vulnerability of the second statement, but also makes the program look more natural, especially in NoSQL (e.g. MongoDB), the variable type must match the field type to be able to.

As you can see 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 conditional restriction of not being able to use special symbols, similar to whereusername=’plhwin’ such an attack The attack statement like whereusername=’plhwin’ can’t be executed, but the hacker can convert the string to hexadecimal encoded data or use the char function to convert it, which can also achieve the same purpose. And because of the existence of SQL reserved keywords such as ‘HAVING’ and ‘ORDERBY’, even the filtering methods based on black and white lists will still be more or less problematic, so are there any other ways to defend against SQL injection?

3. Bind variables and use precompiled statements

MySQL’s mysqli driver provides support for precompiled statements, and different programming languages have methods for using precompiled statements. header(‘Content-type:text/html;charset=UTF-8’);

$username=isset($_GET[‘username ‘])? $_GET[‘username’]:”;

$userinfo=array();

if($username){

//Use the mysqli driver to connect to the demo database

$mysqli=newmysqli(“localhost”, “root”, “root”,’demo’);

$sql =”SELECTuid,usernameFROMuserWHEREusername=?” ;

$stmt=$mysqli->prepare($sql);

//Bind variables

$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, our program does not use the The addslashes function is not used in our program, but the browser does not get any results from running http://localhost/test/userinfo2.php?username=plhwin’AND1=1–hack, indicating that the SQL vulnerability does not exist in this program.

In fact, binding variables to pre-compiled statements is the best way to prevent SQL injection. The semantics of SQL statements using pre-compiled statements don’t change, and in SQL statements, variables are represented by a question mark ? In the SQL statement, the variable is represented by a question mark, so even if the hacker has more skills, he can’t change the structure of the SQL statement. Like in the above example, the plhwin’AND1=1–hack

parameter passed by the username variable will only be used as the username string to interpret the query, which fundamentally eliminates the occurrence of the SQL injection attack.

Database information encryption security

I believe that we are still fresh in the memory of the 2011 CSDN drag library incident, this incident led to the CSDN in the cusp of being scolded by the reason is that they are even explicitly store the user’s password, which triggered the technology sector of the security of the user’s information security, especially password security is a strong concern, we are in the SQL injection to prevent the occurrence of at the same time, we should also be prepared to prevent the occurrence of SQL injections. While SQL injection is happening, we should also save for a rainy day, maybe the next one to be dragged to the library is you, who knows.

In Web development, traditional encryption and decryption can be roughly divided into three kinds:

1, symmetric encryption:

that is, the encrypting party and decrypting party are using the same encryption algorithm and key, the preservation of the key of this program is very critical, because the algorithm is public, while the key is confidential, once the key is leaked, the hacker can still be easily decrypted. Common symmetric encryption algorithms are: AES, DES, etc.

2. Asymmetric encryption:

This is the use of 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 using the public key, the same data encrypted with the public key must be decrypted using the corresponding private key, common asymmetric encryption algorithms are: RSA, and so on.

3, irreversible encryption:

The use of hash algorithms so that the data encryption can not be decrypted back to the original data, such hash algorithms are commonly used: md5, SHA-1 and so on.

In our example code above the login system, $md5password=md5($password); from this code you can see the use of md5 irreversible encryption algorithm to store the password, which is also commonly used in the industry over the years password encryption algorithms, but this is still not safe. Why?

This is because md5 encryption has a feature that the same string is generated after the md5 hash is computed, and since the industry has been using this encryption for a long time, hackers have prepared their own powerful md5 rainbow tables to reverse match the pre-encryption string. These rainbow tables for reverse MD5 encryption can be found everywhere on the Internet, and they can be found in Google use md5 decryption as a keyword search, a moment to find md5 online cracking site, we insert the user data when the MD5 encryption string e10adc3949ba59abbe56e057f20f883e fill in, instantly get the encryption of the password before the encryption: 123456. of course, not every one can be successful, but It’s safe to assume that this rainbow table will get better and better.

So we have an urgent need for a better way to irreversibly encrypt password data, the usual practice is to determine a different password for each user to add salt (salt), and then mixed with the user’s real password md5 encryption, such as the following code:

<?php

/// The user registration time to set the password

$password=$_POST[‘password’];

//md5 encryption, the traditional practice of encrypted strings directly into the database, but this is not enough, we continue to improve

$ passwordmd5=md5($password);

//Generate different password salts for the user, the algorithm can be different according to your business needs

$salt=substr(uniqid(rand()),-6);

///New encrypted string contains password salt

< p>$passwordmd5=md5($passwordmd5.$salt);

Summary

1, do not arbitrarily turn on the error display of the Webserver in the production environment.

2, never trust variable input from the user side, there is a fixed format of the variables must strictly check the corresponding format, there is no fixed format of the variables need to quote and other special characters necessary to filter the escape.

3. Use pre-compiled SQL statements that bind variables.

4, do a good job of database account rights management.

5. Strictly encrypt confidential user information.

How to defend against SQL injection

Tags:

Principles of SQL Injection and How to Avoid It

The so-called SQL injection is the ultimate goal of tricking the server into executing malicious SQL commands by inserting SQL commands into the query strings of Web form submissions or inputs to domain name or page requests. Specifically, it is the ability to leverage an existing application to inject (malicious) SQL commands into a backend database engine for execution, which can be obtained by entering (malicious) SQL statements into a Web form to get a database on a Web site that has a security vulnerability, rather than executing the SQL statements as the designer intended. [1] For example, many previous movie and TV websites have leaked VIP member passwords mostly by submitting query characters through WEB forms, which are particularly vulnerable to SQL injection attacks.

According to the relevant technical principles, SQL injection can be categorized into platform layer injection and code layer injection. The former is caused by insecure database configurations or vulnerabilities in the database platform; the latter is mainly due to programmers not filtering the input meticulously, thus executing illegal data queries. Based on this, the causes of SQL injection are usually manifested in the following aspects: ① improper type handling; ② insecure database configuration; ③ unreasonable query set handling; ④ improper error handling; ⑤ inappropriate handling of escaped characters; ⑥ improper handling of multiple commits.

Injection methods:

1. Guess the table name, column name, etc.

First guess the name of the table

And(Selectcount(*)from table name)<>0

Guess the name of the column

And(Selectcount(column name)from table name)<&gt ;0

Or it could be like this

Andexists(select*from table name)

Andexists(selectcolumnnamefrom table name)

Returns the correct one, then the table name or column name that was written is the correct one

2. Backend Authentication Bypass Vulnerability

Authentication bypass vulnerability is ‘or’=’or’ background bypass vulnerability, the use of AND and OR arithmetic rules, thus causing logical errors in the background script

For example, the administrator’s account password are admin, then Then for example, the database query statement in the background is

user=request(“user”)

passwd=request(“passwd”)

sql=’selectadminfromadminbatewhereuser= ‘&”’&user&”&” andpasswd=’&”’&passwd&”’

Then if I use ‘or’a’=’a for the username password then the query becomes

selectadminfromadminbatewhereuser= ”or’a’=’a’ andpasswd=”or ‘a’=’a’

In that case, according to the rules of arithmetic, there are a total of 4 query statements here, so the query result is false OR true AND false OR true, counting AND and then OR, with the final result being true

. This allows you to get into the background

How to prevent it? To summarize, here are the main points:

1. Never trust user input. Check the user’s input, either by regular expressions, or by limiting the length; convert single quotes and

double “-“, etc.

2. Never use dynamic collocation sql, you can use parameterized sql or directly use stored procedures for data query access.

3. Never use database connections with administrator privileges, use separate database connections with limited privileges for each application.

4. Do not store confidential information directly, encrypt or hash away passwords and sensitive information.

5. The application of the exception should give as few hints as possible, it is best to use customized error messages on the original error message packaging

6. sql injection detection method is generally taken to the auxiliary software or website platform to detect the general use of software sql injection detection tool jsky, website platform on the Yisi website security platform detection tool. MDCSOFT

SCAN and so on. The use of MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks and so on.

Waiting for the test ……..

Copyright: This article is the blogger’s original article, without the blogger’s permission may not be reproduced.

Principles of SQL injection and how to avoid it

Tags: sql injection storage structure filterselect