Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to prevent SQL injection, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

SQL injection means that web applications do not judge or filter laxly the validity of user input data. Attackers can add additional SQL statements to the end of query statements defined in advance in web applications and implement illegal operations without the administrator's knowledge, so as to deceive the database server into executing unauthorized arbitrary queries and further get the corresponding data information.

1. SQL injection case

Simulate a SQL injection case of user login, in which the user enters the user name and password on the console, and then uses Statement string concatenation to realize the user login.

1.1 create user tables and data in the database first

-create a user table CREATE TABLE `users` (`id` INT (11) NOT NULL AUTO_INCREMENT, `username` VARCHAR (20), `password` VARCHAR (50), PRIMARY KEY (`id`) ENGINE=INNODB DEFAULT CHARSET=utf8;-- to insert data INSERT INTO users (username, `password`) VALUES ('Zhang Fei', '123321'), ('Zhao Yun', 'qazxsw'), (' Zhuge Liang', '123Qwe') INSERT INTO users (username, `password`) VALUES ('Cao Cao', '741258'), ('Liu Bei', 'plmokn'), (' Sun Quan','! @ # $% ^');-- View data SELECT * FROM users

1.2 write a login program

Import java.sql.*;import java.util.Scanner;public class TestSQLIn {public static void main (String [] args) throws ClassNotFoundException, SQLException {Class.forName ("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=UTF-8"; Connection conn = DriverManager.getConnection (url, "root", "123456"); / / System.out.println (conn) / / get statement execution platform object Statement Statement smt = conn.createStatement (); Scanner sc = new Scanner (System.in); System.out.println ("Please enter user name:"); String userName = sc.nextLine (); System.out.println ("Please enter password:"); String password = sc.nextLine () String sql = "select * from users where username ='" + userName + "'and password ='" + password + "'"; / / print out SQL System.out.println (sql); ResultSet resultSet = smt.executeQuery (sql); if (resultSet.next ()) {System.out.println ("Login successful!") ;} else {System.out.println ("wrong username or password, please re-enter!!") ;} resultSet.close (); smt.close (); conn.close ();}}

1.3 normal login

Enter the correct user name and password and prompt "login successful"

1.4 login failed

When you enter an incorrect user name or password, prompt "wrong user name or password, please re-enter"

1.5 Analog SQL injection

The stitching string contains or '1users and passwords that are always valid, so all records will be taken out even if the previous users and passwords do not exist, thus prompting "login successful".

1.6 SQL syntax error

When stitching is used, errors such as SQL syntax errors may occur, such as

two。 Solution

Using Statement, users can change the true meaning of SQL through string concatenation, resulting in the risk of SQL injection. To solve the problem of SQL injection, you can preprocess the object PreparedStatement instead of Statement.

1.1 write a new program

Import java.sql.*;import java.util.Scanner;public class TestSQLIn {public static void main (String [] args) throws ClassNotFoundException, SQLException {Class.forName ("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=UTF-8"; Connection conn = DriverManager.getConnection (url, "root", "123456"); / / System.out.println (conn) / / get statement execution platform object Statement / / Statement smt = conn.createStatement (); Scanner sc = new Scanner (System.in); System.out.println ("Please enter user name:"); String userName = sc.nextLine (); System.out.println ("Please enter password:"); String password = sc.nextLine (); String sql = "select * from users where username =? And password =? "; / / System.out.println (sql); / / ResultSet resultSet = smt.executeQuery (sql); PreparedStatement preparedStatement = conn.prepareStatement (sql); preparedStatement.setString (1m username); preparedStatement.setString (2minute password); ResultSet resultSet = preparedStatement.executeQuery (); if (resultSet.next ()) {System.out.println (" Login successful! ") ;} else {System.out.println ("wrong username or password, please re-enter!!") ;} preparedStatement.close (); resultSet.close (); / / smt.close (); conn.close ();}}

2.2 normal login

2.3 user name and password error

When the user name or password is entered incorrectly, it will prompt "wrong user name or password, please re-enter"

2.4Analog SQL injection

According to the previous situation, SQL injection is written, and there is no SQL injection after the test.

2.5 simulated SQL syntax error

After using the preprocessing class, typing with single or double quotation marks will not cause SQL syntax errors.

3. Summary

The main differences between Statement and PreparedStatement are as follows:

Statement is used to execute static SQL statements. When executing, you must specify a prepared SQL statement.

PrepareStatement is a precompiled SQL statement object, which can contain a dynamic parameter "?" or "?" when executed. Set parameter values dynamically

PrepareStatement can reduce the number of compilations and improve database performance.

Thank you for reading this article carefully. I hope the article "how to prevent SQL injection" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.