Before writing the code, pass an organized String type Hql or Sql statement to the background for execution.

This is actually a very stupid approach! ! ! !

Give a chestnut~~

Let’s imitate the user login scenario:

A common approach is to obtain the user at the front desk The name and password are dynamically spliced ​​into the query statement as strings, and then the database query is called. If the query result is not null, it means that the user exists, and the login is successful, otherwise the login fails!

Normally the user enters the account number 123456 and password 123 (assuming it is a wrong password or the user does not exist at all)

usernameString//前台输入的用户名passwordString//前台输入的密码//hql语句String queryString = "from User t where t.username= " + usernameString + " and  t.password="+ passwordString;//执行查询List result = session.createQuery(queryString).list();

For normal user input, the sql statement is spliced ​​into: from User t where t.username=123456 and t.password=123 ;

This is a normal sql statement. You can query the database to verify whether this user data exists.

but!

If the user enters: 123 or 1=1 in the password input box, it is passed into the background as a string

The sql statement is spliced ​​into: from User t where t.username=123456 and t.password=123 or 1=1;

Once Add or 1=1, then this sql will always be true! ! ! More serious cases include deleting tables in the database and tampering with information, which is extremely serious! ! !

#Let’s explain why we get SQL injection?

The reason for sql injection is that strings are spliced ​​together to form sql statements, and sql statements are not precompiled or bound variables are used.

But the deeper reason is that the string entered by the user is executed as a "sql statement".

For example, the above String queryString = "from User t where t.username= " + usernameString + " and t.password="+ passwordString;

We hope that the username and password values ​​entered by the user will only be passed into the database as a string literal value for execution.

But when you enter: 123 or 1=1, or 1=1 is not used as the literal value of where id=, but as The sql statement is executed. So its essence is to execute the user's input data as a command.

##SQL Defense

Basically everyone knows that adopt Precompiling sql statements and binding variables are the best ways to defend against sql injection. To prevent SQL injection, avoid piecing together SQL statements! ! !

In actual projects, we generally use various frameworks, such as ibatis, hibernate, mybatis, etc. They generally default to sql precompiled. For ibatis/mybatis, if you use the form #{name}, then it is sql precompiled. If you use ${name}, it is not sql precompiled.

There are two ways to bind parameters: use positional parameter (used in query string?) or named parameter (used in query string: ).

Use named parameter

usernameString//前台输入的用户名passwordString//前台输入的密码//hql语句String queryString = "from User t where t.username：usernameString and t.password: passwordString";//执行查询List result = session.createQuery(queryString)
                      .setString("usernameString ", usernameString )
                      .setString("passwordString", passwordString)
                      .list();

Use positional parameter

usernameString//前台输入的用户名passwordString//前台输入的密码//hql语句String queryString = "from User t where t.username=? and t.password=?";//执行查询List result = session.createQuery(queryString)
                      .setString(0, usernameString )
                      .setString(1, passwordString)
                      .list();

usernameString//前台输入的用户名passwordString//前台输入的密码//hql语句String queryString = "from User t where t.username：usernameString and
t.phone:usernameString and t.password: passwordString";//执行查询List result = session.createQuery(queryString)
                      .setString("usernameString ", usernameString )
                      .setString("passwordString", passwordString)
                      .list();

usernameString//前台输入的用户名passwordString//前台输入的密码//hql语句String queryString = "from User t where t.username：usernameString and t.password: passwordString";//执行查询return getHibernateTemplate().find(queryString, usernameString, passwordString);

The above is the detailed content of How to use Hibernate to prevent SQL injection. For more information, please follow other related articles on the PHP Chinese website!