Defense against SQL injection

1. domain-driven security

Domain-driven security is a code design approach. The idea is to transform an implicit concept into a display, an object-oriented approach, which is an abstraction of a concept into a class in which the properties of a class are constrained by methods. Whether it is a string, what letter it contains, and so on. Encapsulates the original string.

The benefit is that you only need to unit-Test the class and make sure that the class is created when you need to use that implicit concept in your code. The most important thing is to keep the input data fixed bit data. Preprocessing is done using parameterized statements. A parameterized statement replaces a parameter in an SQL statement with a placeholder, and the program calls the appropriate interface to pass the parameters to the database when the database finishes compiling only the parameters. So you can ensure that the data entered by the user must not be mistaken for the SQL statement to execute. But there's a lot of things that can't be preprocessed.

For situations where preprocessing cannot be used, such as limit, it is often used in business logic such as paging and searching. You cannot use pre-processing queries, you need to encode the input data or filter it strictly. For example, the number can only be 0-9, using the white list principle to filter.

PS: parameterization can only parameterize the data section and cannot be parameterized with keywords and identifiers. This is the limitation of preprocessing, and from the point of view of attackers, you should look for points where preprocessing cannot be used for testing. From the defense, you have to use encoding, character filtering, regular matching, whitelist blacklist policy to filter the input.

2 . preprocessing for each language

(1) Java preprocessing:

The Java Connection database has two usages, the first of which is to connect to the operational database by means of jdbc+sql statements. Or connect to the operational database by Hibernat the lightweight mapping framework.

Two ways of Understanding:

1. With Jdbc+sql, you can flexibly control business logic by using it for small projects. However, it is particularly redundant, and there are many redundant SQL statements in the DAO layer. Makes the code difficult to maintain and debug. But now it's all about using the spring framework. The stand-alone architecture DAO layer encapsulates the operations of the persistence layer according to its own business. This approach is much more.

The following is a complete preprocessing query method, not encapsulated.

Public ResultSet preparequery (String sql,object []param) {

int num=findnum (SQL);

Try {

Getconnection ();

Pst=connection.preparestatement (SQL);

for (int i=1;i<=num;i++)

Pst.setobject (1,param[i-1]);

Res=pst.executequery ();

}catch(Exception e) {

System. out. println (E.getmessage ());

}

return Res;

}

2. There is also a database through object mapping through Hibernat. In fact, Hibernat is a near-step encapsulation of JDBC, a lightweight framework that does not need to inherit a class or interface like struts, and is relatively straightforward to configure. It replaces the jdbc+sql pattern with its own mapping configuration file. There are mainly *.properties,*cfg.xml core configuration files, *hbm.xml mapping files and. Java mapping classes. However, direct use of Hibernat can cause query attacks. Although it also has its own pretreatment.

Package com.liang.hibernate;

Import Java.util.Date;

To create a mapping class

public class User {

Private String ID;

private String name;

private String password;

Private Date Createtime;

Private Date Expiretime;

}

Provide the User.hbm.xml file to complete the Entity class mapping

<span style= "FONT-SIZE:12PX;" ><?xml version= "1.0"?>

<! DOCTYPE hibernate-mapping Public

"-//hibernate/hibernate Mapping DTD 3.0//en"

"Http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<!--generate a database table that defaults to user--

<class name= "Com.liang.hibernate.User" >

<id name= "id" >

The core idea of the <!--algorithm is to combine the network card of the machine with a random number in local time to generate the GUID--

<gnerator class= "UUID" ></generator>

</id>

<property name= "Name" ></property>

<property name= "Password" ></property>

<property name= "Createtime" type= "date" ></property>

<property name= "Expiretime" type= "date" ></property>

</class>

To add the User.hbm.xml file to the Hibernate.cfg.xml file

<! DOCTYPE hibernate-configuration Public

"-//hibernate/hibernate Configuration DTD 3.0//en"

"Http://hibernate.sourceforge.net/hibernate-configuration-3..dtd

<session-factory>

<!--setting up a database driver

<propertyname= "Hibernate.connection.driver_class" >com.mysql.jdbc.Driver<property>

<!--set the database URL--

<property name= "Hibernate.connection.url" >jdbc:mysql://localhost:3306/hibernate_first</property>

<!--database user name--

<property name= "Hibernate.connection.username" >root</property>

<!--database Password--

<property name= "Hibernate.connection.password" >123456</property>

<!--Specify the dialect of the corresponding database, hibernate specifies a dialect for each database, in order to better fit the various relational databases dialect--

<property name= "Hibernate.dialect" >org.hibernate.dialect.MySQLDialect</property>

<!--map Files--

<mapping resource= "Com/liang/hibernate/user.hbm.xml"/>

</session-factory>

Write the tool class Exportdb.java, generate the DDL for HBM, i.e. HBM2DDL

Package com.liang.hibernate;

Import org.hibernate.cfg.Configuration;

Import Org.hibernate.tool.hbm2ddl.SchemaExport;

/**

* Generate DDL from HBM

* @author Liang

*

*/

public class exportdb{

public static void Main (String[]args) {

Read Hibernate.cfg.xml file by default

Configuration cfg = new configuration (). Configure ();

Generate and output SQL to file (current directory) and database

Schemaexport export = new Schemaexport (CFG);

Export.create (True, true);

}

}

Before testing, set up the database Hibernate_first in advance and test it as follows:

SQL statements printed by the console:

drop table if exists User

CREATE TABLE User (ID varchar (255) NOT NULL, name varchar (255), Password varchar (255), Createtime date, Expiretime date, p Rimary Key (ID)

(5) Create client-side classes, add user data to MySQL

Package com.liang.hibernate;

Import Java.util.Date;

Import org.hibernate.Session;

Import Org.hibernate.SessionFactory;

Import org.hibernate.cfg.Configuration;

public class Client {

public static void Main (String[]args) {

Read Hibernate.cfg.xml file

Configuration cfg = new configuration (). Configure ();

Establish Sessionfactory

Sessionfactory factory =cfg.buildsessionfactory ();

Get session

Session session = NULL;

try{

Open session

Session = Factory.opensession ();

Open transaction

Session.begintransaction ();

User user = new user ();

User.setname ("Jiuqiyuliang");

User.setpassword ("123456");

User.setcreatetime (New Date ());

User.setexpiretime (New Date ());

Save User Object

Session.save (user);

Commit a transaction

Session.gettransaction (). commit ();

}catch (Exception e) {

E.printstacktrace ();

Rolling back a transaction

Session.gettransaction (). rollback ();

}finally{

if (session! = NULL) {

if (Session.isopen ()) {

Close session

Session.close ();

}

}

}

}

}

Preprocessing queries:

String Sql= "SELECT * from Users where username=? and password=? "

Query lookupuser=session.createquery (SQL);

Lookupuser.setstring (0,username);

Lookipuser.setstring (1,password);

List rs=lookupuser.list ();
Right-click Debug Run, after the test is complete, we query the test results:

5, in order to observe the log output of hibernate during debugging, it is best to add the log4j.properties configuration file, create a new log4j.properties profile in Classpath or copy the configuration file to src for easy debugging.

3. The contents are as follows:

<span style= "FONT-SIZE:12PX;" >### Direct log messages to stdout # # #

Log4j.appender.stdout=org.apache.log4j.consoleappender

Log4j.appender.stdout.target=system.out

Log4j.appender.stdout.layout=org.apache.log4j.patternlayout

Log4j.appender.stdout.layout.conversionpattern=%d{absolute}%5p c{1}:%l-%m%n

# # Direct messages to File Hibernate.log # # #

#log4j. Appender.file=org.apache.log4j.fileappender

#log4j. Appender.file.file=hibernate.log

#log4j. appender.file.layout=org.apache.log4j.patternlayout

#log4j. Appender.file.layout.conversionpattern=%d{absolute}%5p%c{1}:%l-%m%n

# # # Set Log levels-for more verbose logging "info" to ' Debug ' # # #

Log4j.rootlogger=warn, stdout</span>

(2). NET (C #)

Roughly the same syntax as Java

Sqlconnection sql=new Sqlconnection (connectionString)//Get connection

String sql= "SELECT * from Userswhere @[email protected]" Cmd=new Sqlcommand (Sql,con); SQL statement with placeholders instead of arguments

Cmd. Parament.add ("parameter name", data type, length)//Add Parameter property

cmd parament.value["@username"]=username; Adding parameter Properties

Reader=cmd.executereader ();//Execute SQL

(2) PHP parameterized statements (MYSQLI,PEAR:MDB2,PDO)

Mysqli Bag

$con =new mysqli ("localhost", "username", "password", "db");

$sql = "SELECT * from Users where username=? and password=? ";

$cmd = $con->prepare ($sql);

Adding parameters to an SQL query

$cmd->bind_param ("ss", $username, $password);

$cmd->execute ();

Or PHP5.5 after a simpler preprocessing $result =pg_query_params ("SELECT * from Users WHERE username=$1 and password=$2", Array ($username, $ password));

Pear:mdb2

$mdb 2=&mdb2::factory ($DSN);

$sql = "SELECT * from users WHERE username=? and password=? ";

$types =array (' text ', ' text ');

$cmd = $mdb->prepare ($sql, $types, Mds2_prepare_manip);

$data =array ($username, $password);

$result = $cmd->execute ($data);

Pdo

$sql = "SELECT * from uses WHERE username=:username and" + "password=:p assword";

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

Binding values and data types

$stmt->bindparam (': Username ', $username, PDO::P aram_str,12);//placeholder, value, type, length

$stmt->bindparam (': Username ', $username, PDO::P aram_str,12);

$stmt->execute ();

In addition to the Web development language above, there are ios,android,h5 that can pre-process the database.

3. Input validation

White list:

The whitelist principle takes into account the following factors, known values, data types, data size, data range, data content. These aspects are best constrained to restrict input data, which is slightly more difficult for large character-level restrictions. But SQL injections are mostly English letters or percent semicolons and numbers.

Try to use the whitelist, the security filtering in the client browser is unreliable because the data will be tampered with. Black-and-white list validation can be used at the WAF layer. Ensure the use of parameterized statements. The database is encoded and read data encoded.

(1) with a known value: to form a list and input values to verify, in the database can be describe TableName; to get the columns of the table, if you need to parameterize the column but can not be preprocessed by this method. This is the case for list names.

(2) Indirect input: In the acceptance of the data only the corresponding index, in the banking business is often used in this way, for the user card number, not directly transfer card number, but the transfer of the database index, through the list library and corresponding to its card number. If the index is manipulated, it will cause serious impact.

Blacklist:

A blacklist is a filter for unsafe characters, and regular expressions can also be used, ' |%| --|;| /\*|\\\*|_|\[@|xp_

The blacklist cannot be used in isolation because the non-secure characters are many and complex and cannot be updated in a timely manner. Even if parameterized queries are used, no input filtering is possible. Because parameterization can cause unsafe characters to be injected directly into the database, causing other problems such as second-order injection.

PS: For verification failure processing, recovery or error, recovery is not very good, the hacker constructs a filter can not iterate caused by multi-character attacks, the error is to redirect the page to an error page, affecting the user experience, but can be added to the front desk verification, to ensure that the real user is not redirected to the error page.

Data entry Validation in Java

JSF framework, a framework that is not popular. Comes with some validation mechanisms. Developing the web like a desktop program

String regex= "[^a-za-z0-9]"; Pattern P=pattern.compile (regEx);

if (username!=null) {m=p.matcher (username);

Username=m.replaceall (""). Trim (); else if (password!=null) {

M=p.matcher (password); Password=m.replaceall (""). Trim ();

Validation of all programs is done primarily through regular expressions, except that the methods are called differently.

Input Validation in PHP:

Preg_match (regex,matchstring): Use regular expression regex to perform regular expression matching on matchstring,

Is_<type> (Input): Check whether the input is <type>, such as Is_numeric

strlen (Input): Check the length of the input.

Demo

$username =$_post[' username '];

if (!preg_match ("/^[a-za-z] {8,12}$/d", $username) {

Validation failed

}

In general, PHP framework thinkphp does not need this way, but instead uses the I (input) function for filtering, and its global filtering is done by default. The last parameter of the I function is the filtering method, which can filter the ip,email, expression, etc., and the global filter Var_filters is an iterative filter. Only if the last parameter is NULL, no filtering is done.

Coding:

Cannot use encoding for filtering using preprocessing

For Oracle: Replace single single quotes ' with two ', in PL/SQL, you need to change to 4 ' ' because the single quote is a string terminator and requires a reference. Input validation can also be performed using Dbms-assert. Provides 7 functions to validate different inputs

Excute immediate ' SELECT ' | | Sys.dbms_assert. Simple_sql_name (FIELD) | | ' From ' | | (Sys.dbms_assert. Schema_name (OWNER), FALSE) | | '. ' | | Sys.dbms._assert. Qualified_sql_name (TABLE);

Functions provided by Dbms_assert

For Sqlserver:select * from UserInfo where username like ' a\% ' escape ' \ '

Escape () specifies the escape character, the other is to replace the single quotation mark, or to escape the single quotation mark with Eacape but in the process of ESACPE, escape itself will be contaminated with quotation marks into a string, so this practice is not desirable, PL/SQL is the same as Oracle.

For MySQL: Also ' carry \ ' escape, replace in stored procedure (@sql, ' \ ', ' \\\ ')

In general, there are a few points to take into consideration when dealing with inputs:

1. Using parameterized preprocessing

2. Encode and escape sensitive characters

3. Whitelist blacklist filter For input

4. Use database functions and custom functions to validate non-data keywords and identifiers before entering the database.

5. The overall project uses harmonized coding.

6. Filtering and intercepting attacks through WAF at the network layer

Avoid SQL injection on the design

1. To access the database through a stored procedure:

In database access, SQL statements constructed by the program are passed into the database for execution, so that dynamic SQL statements have greater permissions than stored procedures, so you can only provide an interface to the application for the stored procedure, and the stored procedure cannot insert,update,delete the database data. It also mitigates the impact of SQL injection. In this way, the attacker could not invoke the system's stored procedures, and it would be more difficult to extract power and infiltrate the intranet.

The downside is that programming with stored procedures everywhere is cumbersome and requires a sophisticated and practical persistence layer that is more complex.

2. To access the database using the abstract layer:

Hibernate,aciverecord,entity framework,ado.net,jdbc,pdo Such an abstraction layer to access the database, programmers do not have to write SQL statements, through parameters to pass the data exchange with the database.

3. Handling sensitive data reduces risk

Passwords in the database, user personal information, and other sensitive data should be kept separate, and the processing of such data is different from normal.

Password: The user password and other information using SHA256, such as one-way salt-based irreversible algorithm encryption, in the process of logging plaintext encryption and cipher to complete the verification, and the salt and hash separately saved. Sensitive information like this can be taken as above.

Financial information: Using FIPS-certified algorithms for encryption,

Archive: Periodically archive and purge database information

Avoid obvious object names: such as password is called Password,kennword (DE), MOTDEPASS,MDP (law), Wachtwoord (Netherlands), Senha (Portugal), Haslo (Poland)

Honeypot: honeypot, create false information table luring attack, will be the first time to notify the administrator

Parameterized statements are parameterized through precompilation, so keywords and identifiers cannot be inserted after compilation

Use whitelist in the application, blacklist in Web Firewall

Security Defense Platform WAF

1. Configurable rule set: has its own instructions to configure defense policies, such as regular match, black and white list, etc.

2. Request coverage: For Cookie,url,request heard, etc. for protection, wide coverage

3. URL,WAF provides a number of encoding conversion functions for encoding attack response and matches each rule to support the Lua language to customize the rules

4. Response Analysis: Match exception responses against rule set out-of-band rules, blocking them from being returned to the user

5. Intrusion detection: Check the details of SQL injection through the log, which is not dependent on the Web application.

Lock Application Data:

1. Log in to the database with lower privileges:

2. Isolate Database login: Set permissions for different applications to die

3. Revoke the Public License

4. Using Stored Procedures

5. Using encryption technology

High risk database functions and stored procedures

Defense against SQL injection