0x00 Preface

This article records my way of learning, directly into the body.

0x01 text

Why does SQL injection occur? For me, it will be summed up as one sentence: "the SQL statement executed by dynamic splicing contains untrusted data."
What is dynamic splicing? Look at the following SQL statement:

select * from "+param_table+" where name='"+param_name+"'";

See the '+' sign in the statement, which means that param table and param name are not written in the statement, but I can pass parameters to achieve some of my purposes.
So if I have a student table:

teacher table:

I want to find out about hacker
Then there will be the following code:

String param_table = "student";
String param_name = "hacker";
Statement stmt = conn.createStatement();
ResultSet rs =  stmt.executeQuery("select * from "+param_table+" where name='"+param_name+"'");
while(rs.next()) {
     out.println(rs.getString(1)+"/"+
                 rs.getString(2)+"/"+
                 rs.getString(3));
           		 }

So a statement is formed:

select * from student where name='hacker';

In this way, you can query the information of hacker:

But if I change hacker to hacker's or 1=1 × 3:
String param_name = "hacker' or 1=1#";
Then all the data in the student table is dump ed:

Then you can change student to student union select * from teacher, and the data in the teacher table will also be dump:

How to protect? This is the key point. When I used to dig SQL injection, I just gave the manufacturer such advice, but it may be a very vague concept for the manufacturer:
Now I write down the instance, so as to deepen my understanding of SQL injection at the same time.
1. Precompile:
Here, the PreparedStatement class is used for precompiling, and the following code will be available:

String param_table = "student";
String param_name = "hacker";
String stmt = "select * from ? where name= ?";
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_table);
ps.setString(2,param_name);
ResultSet rs =  ps.executeQuery();
while(rs.next()) {
      out.println(rs.getString(1)+"/"+
                  rs.getString(2)+"/"+
                  rs.getString(3));

Then there was an error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''student' where name= 'hacker'' at line 1

Finally, after debugging, it is found that param_table cannot be bound, and field names cannot be bound, so precompilation and query may be performed by splicing. The code is as follows:

String param_table = "student";
String param_name = "hacker";
PreparedStatement ps = conn.prepareStatement("select * from "+param_table+" where name=?");
ps.setString(1,param_name);
ResultSet rs =  ps.executeQuery();
while(rs.next()) {
      out.println(rs.getString(1)+"/"+
                  rs.getString(2)+"/"+
                  rs.getString(3));
            }

But param_table=student still generates injection here. If it is modified to:

String param_table = "student union select * from teacher";

Then:

This injection is more exotic. The where clause is spliced into the statement querying the teacher table:

Then I can only write the student in the statement:

String param_name = "hacker";
String stmt = "select * from student where name=?";
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_name);
ResultSet rs =  ps.executeQuery();
while(rs.next()) {
      out.println(rs.getString(1)+"/"+
                  rs.getString(2)+"/"+
                  rs.getString(3));
            }

In this case, change param#name to hacker's or 1=1ා:
Then we will query hacker 'or 1=1 × as the table name. We can't find this table, of course, there is no echo:

2. Stored procedure:
There is a stored procedure that operates on the student table:

create procedure `getstudent`(in aname varchar(20),out uname varchar(20),out uage int(11),out usex varchar(10))
begin
select * from student where name=aname into uname,uage,usex;
end;

Then we can use the CallableStatement class to prevent injection. The code is as follows:

String param_name = "hacker' or 1=1#";
CallableStatement cs = conn.prepareCall("{call getstudent(?,?,?,?)}");
cs.setString(1,param_name);
cs.registerOutParameter(2,Types.VARCHAR);
cs.registerOutParameter(3,Types.INTEGER);
cs.registerOutParameter(4,Types.VARCHAR);
cs.executeQuery();
out.println(cs.getString(2)+"/"+
			cs.getInt(3)+"/"+
			cs.getString(4));

You can see that SQL injected statements no longer work:

3. White list verification:
The previous precompiled and stored procedures can't operate on the table name. Here, the white list is used to filter the table name. The code is as follows:

String param_table = "student union select * from teacher";
String param_name = "hacker";
String stmt = "";
if(param_table.equals("student")) {
		stmt = "select * from student where name=?";
			}
else if(param_table.equals("teacher")) {
		stmt = "select * from teacher where name=?";
			}
else {
		out.println("table name error!");
			}
PreparedStatement ps = conn.prepareStatement(stmt);
ps.setString(1,param_name);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
      out.println(rs.getString(1)+"/"+
				  rs.getString(2)+"/"+
				  rs.getString(3));
			}

Error will be reported:
4. Code the input
Here I use hexadecimal to code the input. The declaration and definition code of the method are as follows:

public static String bytestoHex(byte[] byteArr) {
			if(byteArr == null || byteArr.length < 1) return "";
			StringBuilder sb = new StringBuilder();
			for(byte t : byteArr) {
				if((t & 0xF0) == 0) sb.append("0");
				sb.append(Integer.toHexString(t & 0xFF));
			}
			return sb.toString().toUpperCase();
		}

Use the method byte2HexStr to code the input param_name, code:

String param_name = "hacker' or 1=1#";
		Statement stmt = conn.createStatement();
		String hex_param_name = bytestoHex(param_name.getBytes());
		out.println("Coded param_name For:"+bytestoHex(param_name.getBytes()));
		ResultSet rs = stmt.executeQuery("select * from student where hex(name)='"+hex_param_name+"'");
		while(rs.next()) {
			out.println(rs.getString(1)+"/"+
						rs.getString(2)+"/"+
						rs.getString(3));
		}

Because hacker's or 1=1 ා is encoded as 6861636B657227206F7220313D3123 and queried as a table name, no other information will be dump:

0x02 conclusion

The safety road is very long. It's important to stick to it!