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

This article mainly introduces "how to effectively prevent SQL injection attacks". In daily operation, I believe many people have doubts about how to effectively prevent SQL injection attacks. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "how to effectively prevent SQL injection attacks". Next, please follow the editor to study!

SQL injection attack is one of the common means for hackers to attack the database. With the development of the application development of BPX S pattern, more and more programmers use this pattern to write applications. However, due to the uneven level and experience of programmers, a considerable number of programmers do not judge the legitimacy of user input data when writing code, which makes the application have security risks. Users can submit a database query code, according to the results returned by the program, get some data he wants to obtain, this is the so-called SQL Injection, that is, SQL injection.

A background

If a university develops a web-based course system that requires students to complete their studies after electing courses, there is a table course in the database, which stores the course selection information and completion of each student. The specific design is as follows:

The data are as follows:

This system uses mysql as the database and uses Jdbc to carry on the related operation of the database. The system provides a function to query the student's course completion, the code is as follows.

@ RestController public class Controller {@ Autowired SqlInject sqlInject; @ GetMapping ("list") public List courseList (@ RequestParam ("studentId") String studentId) {List orders = sqlInject.orderList (studentId); return orders;}} @ Service public class SqlInject {@ Autowired private JdbcTemplate jdbcTemplate; public List orderList (String studentId) {String sql = "select id,course_id,student_id,status from course where student_id =" + studentId Return jdbcTemplate.query (sql,new BeanPropertyRowMapper (Course.class));}}

Demonstration of two injection attacks

1. Under normal circumstances, you only need to input student_id to query a student's selected courses and completion, and the relevant data can be found.

Based on the response result, we will soon be able to write the corresponding sql, as follows:

Select id,course_id,student_id,status from course where student_id = 4

two。 If we want to get all the data in this table, we just need to make sure that the where condition of the above sql is true.

Select id,course_id,student_id,status from course where student_id = 4 or 1 = 1

Set studendId to 4 or 1 = 1 when requesting the interface, so that the where condition of this sql is always true. Sql is also equivalent to the following

Select id,course_id,student_id,status from course

The result of the request is as follows. We got all the data from this table.

3. Query mysql version number and use union to concatenate sql

Union select 1 database (), 1

4. Query database name

Union select 1 database (), 1

5. Query all libraries for the current user of mysql

Union select 1, (SELECT GROUP_CONCAT (schema_name) FROM information_schema.schemata) schemaName,1

After watching the above demonstrations, are you scared? All your data configurations are completely exposed, and in addition, you can do a lot of things, such as updating data, deleting libraries, deleting tables, and so on.

How to prevent sql injection

1. The best way for the code layer to prevent sql injection attacks is to precompile sql

Public List orderList (String studentId) {String sql = "select id,course_id,student_id,status from course where student_id =?"; return jdbcTemplate.query (sql,new Object [] {studentId}, new BeanPropertyRowMapper (Course.class));}

In this way, the parameter 4 or 1 = 1 we pass in will be treated as a student_id, so there will be no sql injection.

two。 Confirm the type of each data, such as a number, and the database must use the int type to store

3. Specify data length, which can prevent sql injection to some extent

4. Strict restriction of database permissions can minimize the harm of sql injection.

5. Avoid responding to some sql exception information directly. When an exception occurs in sql, the custom exception responds.

6. Filter some database keywords contained in the parameters

@ Component public class SqlInjectionFilter implements Filter {@ Override public void doFilter (ServletRequest servletRequest, ServletResponse servletResponse, FilterChain chain) throws IOException, ServletException {HttpServletRequest req= (HttpServletRequest) servletRequest; HttpServletRequest res= (HttpServletRequest) servletResponse; / / get all request parameter names Enumeration params = req.getParameterNames (); String sql = "" While (params.hasMoreElements ()) {/ / get the parameter name String name = params.nextElement () .toString (); / / get the corresponding value String [] value = req.getParameterValues (name); for (int I = 0; I)

< value.length; i++) { sql = sql + value[i]; } } if (sqlValidate(sql)) { throw new IOException("您发送请求中的参数中含有非法字符"); } else { chain.doFilter(servletRequest,servletResponse); } } /** * 关键词校验 * @param str * @return */ protected static boolean sqlValidate(String str) { // 统一转为小写 str = str.toLowerCase(); // 过滤掉的sql关键字，可以手动添加 String badStr = "'|and|exec|execute|insert|select|delete|update|count|drop|*|%|chr|mid|master|truncate|" + "char|declare|sitename|net user|xp_cmdshell|;|or|-|+|,|like'|and|exec|execute|insert|create|drop|" + "table|from|grant|use|group_concat|column_name|" + "information_schema.columns|table_schema|union|where|select|delete|update|order|by|count|*|" + "chr|mid|master|truncate|char|declare|or|;|-|--|+|,|like|//|/|%|#"; String[] badStrs = badStr.split("\\|"); for (int i = 0; i < badStrs.length; i++) { if (str.indexOf(badStrs[i]) >

= 0) {return true;}} return false;}} at this point, the study on "how to effectively prevent SQL injection attacks" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.