Shulou(Shulou.com)06/02 Report--

This article introduces the relevant knowledge of "how to solve the SQL injection problem". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

JDBC:

1. Full name Java Database Connectivity

2. It is the API for Java to access the database, which does not depend on a specific database (database-independent).

3. All Java persistence layer technologies are based on JDBC

Description

In scenarios where JDBC is used directly, if there is a concatenation SQL statement in the code, then injection is likely to occur, such as

/ / concat sql String sql = "SELECT * FROM users WHERE name ='" + name + "'"; Statement stmt = connection.createStatement (); ResultSet rs = stmt.executeQuery (sql)

The safe way to write is to use parameterized queries (parameterized queries), that is, parameter bindings (?) are used in SQL statements. Placeholders) and PreparedStatement, such as:

/ / use? To bind variables String sql = "SELECT * FROM users WHERE name=?"; PreparedStatement ps = connection.prepareStatement (sql); / / Parameter index starts from 1 ps.setString (1, name)

There are also some cases, such as order by and column name, which cannot use parameter binding and need to be filtered manually. For example, the field names of order by are usually limited, so you can use whitelist to limit parameter values.

It should be noted here that the use of PreparedStatement does not mean that injection will not be generated. If there is a concatenated sql statement before using PreparedStatement, it will still result in injection, such as

/ / stitching sql String sql = "SELECT * FROM users WHERE name ='" + name + ""; PreparedStatement ps = connection.prepareStatement (sql)

Normally, the user's input is used as a parameter value, while in SQL injection, the user's input is compiled / interpreted by the database as part of the SQL instruction. When PreparedStatement is used, sql statements with placeholders (?) are compiled only once, and then execution simply replaces placeholders with user input and does not compile / interpret again, thus fundamentally preventing SQL injection problems.

Mybatis

Introduction

1. First class persistence framework

2. Between JDBC (raw SQL) and Hibernate (ORM)

3. Simplify most of the JDBC code, set parameters manually and get results

4. Flexible, users can fully control SQL and support advanced mapping

Description

In MyBatis, use XML files or Annotation for configuration and mapping, mapping interfaces and Java POJOs (Plain Old Java Objects) to database records

XML example

Mapper Interface

@ Mapper publicinterfaceUserMapper {User getById (int id);}

XML profile

SELECT * FROM user WHERE id = # {id}

Annotation example

Mapper publicinterfaceUserMapper {@ Select ("SELECT * FROM user WHERE id= # {id}") User getById (@ Param ("id") int id);}

As you can see, users need to write their own SQL statements, so when used improperly, it can lead to injection problems.

Unlike using JDBC, MyBatis uses # {} and ${} to replace parameter values

When using the # {} syntax, MyBatis automatically generates PreparedStatement, using parameter binding (?) The equivalent JDBC query code for the above two examples is as follows:

String sql = "SELECT * FROM users WHERE id =?"; PreparedStatement ps = connection.prepareStatement (sql); ps.setInt (1, id)

Therefore, # {} can effectively prevent SQL injection. For more information, please see the http://www.mybatis.org/mybatis-3/sqlmap-xml.html String Substitution section.

When using the ${} syntax, MyBatis directly injects the original string, which is equivalent to a concatenated string, resulting in SQL injection, such as:

SELECT * FROM user WHERE name ='${name} 'limit 1

The name value is'or'1, and the actual statement executed is:

SELECT * FROM user WHERE name =''or'1'='1' limit 1

Therefore, it is recommended to use # {} as much as possible, but sometimes, such as order by statements, using # {} can lead to errors, such as:

ORDER BY # {sortBy}

The parameter value of sortBy is name, and when replaced, it becomes:

ORDER BY "name"

Sort by the string "name" instead of the name field. For more information, please see https://stackoverflow.com/a/32996866/6467552.

In this case, you need to use ${}

ORDER BY ${sortBy}

After using ${}, the user needs to filter the input by himself by:

The code layer uses whitelist to limit the values allowed by sortBy. For example, only name and email fields are allowed. If there is an exception, it is set to the default value name. In the XML configuration file, use the if tag to judge.

Mapper interface method

List getUserListSortBy (@ Param ("sortBy") String sortBy)

Xml profile

SELECT * FROM user order by ${sortBy}

Because Mybatis does not support else, you can use choose (when,otherwise) if you need a default value.

SELECT * FROM user order by ${sortBy} order by name

More scenes

In addition to orderby, there are some situations where ${} may be used, which can be avoided using other methods, such as:

Like statement

If you need to use wildcards (wildcard characters% and _), you can add% on both sides of the parameter values at the code layer, and then use # {} to use the bind tag to construct new parameters, and then use # {}

Mapper interface method

List getUserListLike (@ Param ("name") String name)

Xml profile

SELECT * FROM user WHERE name LIKE # {pattern}

The value in the statement is OGNL expression. For more information, please see http://www.mybatis.org/mybatis-3/dynamic-sql.htmlbind section.

Use the SQL concat () function

SELECT * FROM user WHERE name LIKE concat ('%', # {name},'%')

In addition to the injection problem, the user's input needs to be filtered and wildcards are not allowed, otherwise, when there is a large amount of data in the table, assuming that the user's input is%%, a full table fuzzy query will be carried out, which can lead to DOS in serious cases.

IN condition

Use

And # {} Mapper interface method List getUserListIn (@ Param ("nameList") List nameList)

Xml profile

SELECT * FROM user WHERE name in # {name}

Limit statement

1. Just use # {} directly.

2. Mapper interface method

List getUserListLimit (@ Param ("offset") int offset, @ Param ("limit") int limit)

Xml profile

SELECT * FROM user limit # {offset}, # {limit}

JPA & Hibernate

Introduction

JPA:

1. Full name Java Persistence API

2. ORM (object-relational mapping) persistence layer API, which needs a specific implementation

Hibernate:

JPA ORM implementation

Description

There is a misconception that if you use the ORM framework, there will be no SQL injection. In fact, in Hibernate, HQL (Hibernate Query Language) and native sql queries are supported. The former has HQL injection, and the latter has the same injection problem as the previous JDBC. Let's take a look at it.

HQL

HQL query example

Query query = session.createQuery ("from User where name ='" + name + "', User.class); User user = query.getSingleResult ()

The User here is the class name, which is similar to the native SQL. Splicing results in injection.

The correct usage:

Position parameter (Positional parameter)

Query query = session.createQuery ("from User where name =?", User.class); query.setParameter (0, name)

Named parameter (named parameter)

Query query = session.createQuery ("from User where name =: name", User.class); query.setParameter ("name", name)

Named parameter list (named parameter list)

Query query = session.createQuery ("from User where name in (: nameList)", User.class); query.setParameterList ("nameList", Arrays.asList ("lisi", "zhaowu"))

Class instance (JavaBean)

User user = newUser (); user.setName ("zhaowu"); Query query = session.createQuery ("from User where name =: name", User.class); / / User class requires getName () method query.setProperties (user)

Native SQL

There is SQL injection

String sql = "select * from user where name ='" + name + "'"; / / deprecated / / Query query = session.createSQLQuery (sql); Query query = session.createNativeQuery (sql)

Use parameter binding to set parameter values

String sql = "select * from user where name =: name"; / / deprecated / / Query query = session.createSQLQuery (sql); Query query = session.createNativeQuery (sql); query.setParameter ("name", name)

JPA

JPQL (Java Persistence Query Language) is used in JPA, and native sql is also supported, so there are similar problems with Hibernate, so I won't go into details here.

That's all for the content of "how to solve the SQL injection problem". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.