Like SQL Injection in Query

See ibatis in action, which mentions the injection vulnerability when using like for fuzzy search. Example: xml Code <select id = "getSchoolByName" resultMap = "result"> select * from tbl_school where school_name like '% $ name $ %' </select> Java code public List <School> getSchoolByName (String name) throws DataAccessException {List <School> list = (List <School>) getSqlMapClientTemplate (). queryForList ("getSchoolByName", name); return list;} Test case: Java code @ Test public void print () {try {List <School> list = schoolD Ao. getSchoolByName ("Changle Zhongyi % 'or '1%' = '1"); for (School school: list) {System. out. println (school. getName () ;}} catch (Exception e) {e. printStackTrace () ;}use p6spy to view the last generated SQL statement: SQL code sql1: select * from tbl_school where school_name like '% Changle medium %' or '1% '= '1%' sql2: select * from tbl_school where school_name like '% Changle medium %' or '1% '= '1%' where: sql1 is the SQL statement executed by ibatis in preparedstatement, sql2 is the real SQL statement executed by jdbc. In this example The two are the same, because the placeholder used in the map is $ name $, ibatis encounters such a placeholder, and then directly spell the SQL statement, instead of using placeholders in SQL and then giving SQL set paramter (# name # is used, but cannot be used for Fuzzy queries ). The consequence of a project is that if you have an input box on the page that allows you to enter the school name, you can enter the words "Changle 1st % 'or '1%' = '1, then the program will list all the school results. In fact, some schools may have been deleted (using a field tag or fake deletion), and users do not want to see it again or the current user has no permission to view some school information. In ibatis in action, this injection vulnerability is described as a more terrible consequence. delete a table. Modify the Test case as follows: Java code @ Test public void print () {try {List <School> list = schoolDao. getSchoolByName ("Changle Zhongyi"; drop table tbl_test; # "); for (School school: list) {System. out. println (school. getName () ;}} catch (Exception e) {e. printStackTrace () ;}} use p6spy to view the last generated SQL statement: SQL code select * from tbl_school where school_name like '% Changle Zhongyi'; drop table tbl_test; # % 'select * from tbl_school where school_name like '% Changle Zhongyi'; d Drop table tbl_test; # % 'in mysql, # Is the annotator. Copy the following SQL code and run it in phpmyadmin. tbl_test is indeed deleted. However, the execution of this SQL statement with ibatis fails. After debugging the source code of ibatis, we found that ibatis was used to execute the query with preparedstatement. The preceding two SQL statements, but ibatis directly puts the SQL statement "select * from tbl_school where school_name like '% Changle Zhongyi'; drop table tbl_test; # % '" for execution, similar to the following: Java code String SQL = "select * from tbl_school where school_name like '% Changle Zhongyi'; drop table tbl_test; # % '" PreparedStatement ps = conn. prepareStatement (SQL); ps.exe cute (); in this case, an error is reported, and the table tbl_test cannot be deleted... Strange .. Is this book ibatis in action wrong? The above code is all tested in the ibatis2.3.4 environment. I have not tried any previous versions .. But when using ibatis, cannot I like the query? Or do I need to filter user input conditions at the web or service layer? It is too troublesome. Fortunately, ibatis provides another placeholder # When Using PreparedStatement to execute a query, is it used? As a placeholder, and then set paramter .. Change the SQL statement in map to this: (refer to the online SQL statement) SQL code mysql: select * from tbl_school where school_name like concat ('%', # name #, '%') oracle: select * from tbl_school where school_name like '%' | # name # | '%' SQL Server: select * from tbl_school where school_name like '%' + # name # + '%'