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();

Copy after login

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: ).

hibernate supports JDBC style positional parameter (used in query string?), which has the same effect as using 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();

Copy after login

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();

Copy after login

两者比较：positional parameter可读性强不如named parameter的强，而且可维护性差，如果我们的查询稍微改变一点，将第一个参数和第二个参数改变一下位置，

这样我们的代码中涉及到位置的地方都要修改，所以我们强烈建议使用named parameter方式进行参数绑定。

最后，在named parameter中可能有一个参数出现多次的情况，应该怎么处理呢？

在举个栗子~~

我们模仿一下用户登录的场景：这次业务变换，有的网站，手机号可以作为用户名来登录，也能作为手机号本身登录。

常见的做法是将前台获取到的用户名or手机号和密码，作为字符串动态拼接到查询语句中，然后去调用数据库查询~查询的结果不为null就代表用户存在，则登陆成功，否则登录失败！

正常情况下用户输入账号是13812345678和密码123

这里usernameString作为手机号又作为用户名出现了两次，怎么办呢？

大家请看下面代码：

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();

Copy after login

在Hibernate+spring中getHibernateTemplate()返回的对象可以调用find(String queryString, Object value...Object value)来实现named parameter。比如：

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

Copy after login

PS:其实说这么多都是扯淡，因为现在真是商业项目中，没有把密码以明文的方式存入数据库的，基本上都是经过加密以后进行比对。所以不管用户输入什么都会解密成一个字符串。所以，这种SQL注入基本上已经不存在了~~~~

所以还是建议大家在开发中，多规范一下自己的代码，让代码更加健壮！

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!