Using Hibernate to prevent SQL injection, hibernatesql

Using Hibernate to prevent SQL injection, hibernatesql

Write the code before, and input an Hql or SQL statement of the String type to the background for execution.

This is actually a stupid practice !!!!

For example ~~

We mimic the user login scenario:

A common practice is to dynamically splice the username and password obtained from the front-end into the query statement as a string, and then call the database query ~ If the query result is not null, it indicates that the user exists. If the query result is successful, the logon fails!

Under normal circumstances, the user enters the account 123456 and password 123 (assuming the password is incorrect or the user does not exist at all)

UsernameString // The username passwordString input at the front end // The password entered at the front end // hql statement String queryString = "from User t where t. username = "+ usernameString +" and t. password = "+ passwordString; // execute the query List result = session. createQuery (queryString ). list ();

If a normal user inputs the SQL statement, it is spliced: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 the user data exists.

But!

If you enter 123 or 1 = 1 as a string in the password input box

The SQL statement is spliced:From User t where t. username = 123456 and t. password = 123 or 1 = 1;

Once or 1 = 1 is added, this SQL statement is always valid !!! More seriously, you can delete tables in the database and tamper with the information !!!

Let's explain why SQL injection has taken place?

On the surface, SQL injection is caused by concatenating strings to form SQL statements. SQL statements are not pre-compiled and variables are bound.

However, the deeper reason is that the string entered by the user is treated as an "SQL statement" for execution.

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

We want the username and password values entered by the user to be passed into the database for execution only as a string literal value.

However, when the input is: 1When 23 or 1 = 1, or 1 = 1 is not used as the where id =, but as an SQL statement. Therefore, the essence is to execute user input data as commands.

SQL defense

Basically, we all know that using SQL statements to pre-compile and bind variables is the best way to defend against SQL injection.To prevent SQL injection, avoid the use of patchwork SQL statements !!!

In actual projects, we generally adopt various frameworks, such as ibatis, hibernate, and mybatis. Generally, they are pre-compiled by default. For ibatis/mybatis, if the # {name} format is used, it is SQL pre-compilation, and $ {name} is not used for SQL pre-compilation.

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

Hibernate supports JDBC-style positional parameter (which is used in query strings ?), It works the same as using named parameter (used in query strings :).

 

Use named parameter

 

UsernameString // The username passwordString input at the front end // The password entered at the front end // hql statement String queryString = "from User t where t. username: usernameString and t. password: passwordString "; // execute the query List result = session. createQuery (queryString ). setString ("usernameString", usernameString ). setString ("passwordString", passwordString ). list ();

Use positional parameter

UsernameString // The username passwordString input at the front end // The password entered at the front end // hql statement String queryString = "from User t where t. username =? And t. password =? "; // Execute the query List result = session. createQuery (queryString). setString (0, usernameString). setString (1, passwordString). list ();

Comparison:Positional parameterLess readableNamed parameterAnd poor maintainability. If our query changes a little bit, change the positions of the first and second parameters,

In this way, all the locations involved in our code must be modified.We strongly recommend that you bind Parameters Using named parameter.

Finally, a parameter in named parameter may appear multiple times. What should I do?

For example ~~

We mimic the user login scenario: This business transformation, some websites, mobile phone numbers can be used as user names to log on, but also as mobile phone numbers themselves to log on.

A common practice is to dynamically splice the username or mobile phone number and password obtained by the front-end into the query statement as a string, and then call the database to query ~ If the query result is not null, it indicates that the user exists. If the query result is successful, the logon fails!

Normally, the user enters the account 13812345678 and password 123.

Here, usernameString appears twice as the mobile phone number and user name. What should I do?

Please refer to the following code:

UsernameString // The username passwordString input at the front end // The password entered at the front end // hql statement String queryString = "from User t where t. username: usernameString andt. phone: usernameString and t. password: passwordString "; // execute the query List result = session. createQuery (queryString ). setString ("usernameString", usernameString ). setString ("passwordString", passwordString ). list ();

In Hibernate + spring, the Object returned by getHibernateTemplate () can call find (String queryString, Object value... Object value) to implement named parameter. For example:

UsernameString // The username passwordString input at the front end // The password entered at the front end // hql statement String queryString = "from User t where t. username: usernameString and t. password: passwordString "; // execute the query return getHibernateTemplate (). find (queryString, usernameString, passwordString );

PS: in fact, this is all nonsense, because the passwords are not stored in the database in plain text in commercial projects, and are basically compared after encryption. Therefore, no matter what the user inputs, it will be decrypted into a string. Therefore, this SQL Injection basically does not exist anymore ~~~~ We recommend that you standardize your code during development to make the code more robust!