"Ultra vires" caused by one SQL injection

Original text from SecIN community - Author: tkswifty

Relevant background

  in the actual business development, SQL interaction is often an indispensable item in the business system. Mybatis, Hibernate and SpringData JPA are provided in Java to meet the needs of database interaction. However, for various reasons, when dealing with the interaction between application and database, developers use string splicing to construct SQL statements, which leads to the problem of SQL injection. Sometimes, when there is SQL injection in a large number of interfaces and the iteration is difficult, the filter / interceptor is the first choice of many developers. By filtering the relevant SQL keywords, we can avoid further utilization of SQL injection.

   for the above scenarios, it is often necessary to check whether the filter design is rigorous and whether there are fish that have escaped the net, resulting in SQL injection vulnerabilities being exploited by attackers. During the audit of a project some time ago, it was found that there was an "ultra vires" caused by SQL injection. The following is the relevant process.

Mining process

  the system is developed based on spring MVC, and its business is mainly related to resume editing. The interface of relevant problems is mainly in the modification of resume. Generally speaking, this business of modifying personal information mainly transmits two key information in addition to the modification content:

  • Identity certificate userId of the current user
  • Business number of the current user (resume here), resumeId

   when making an interface business request, obtain the key parameter userid related to the business from the current user's identity certificate (generally session), bind the personal user identity, and then obtain the resumeId to be modified from the front end. Finally, when saving the information for SQL interaction, bind the userid obtained in the session with resumeId again to ensure that the user corresponding to userid can only modify his resume. Similar SQL statements are as follows:

UPDATE user_resume SET content='test',user_name='test'{Omit relevant contents} where userId = $userId and resumeId=$resumeId;

  the following are the relevant codes:

The current user's resume is automatically processed by binding the resume to the user's resume object through the user's resume   login method, and then the user's resume is directly update d through the resume Controller's login method. The user's resume is automatically processed through the user's resume   login method:

	@RequestMapping(value = "/updateResume", method = RequestMethod.POST)
	public PagedResult<ResponseRes> updateResume(Resume resume) {
		String userId = (String)session.getAttribute("userid");
		PagedResult<ResponseRes> pageResult = this.resumeService    
		return pageResult;

  check the service layer implementation and call the update method of resumeService:

public PagedResult<ResponseRes> updateResume(Resume resume,String userid) {
		String rusumeId = resume.getId();
				boolean updateStatus = resumeDao.update(resume, userid,resumeId);		
				//Update succeeded, encapsulating the returned results
				//Update failed
		return updateResule;

  the encapsulated resume content and the update method of userId and ResumeId passed into resumeDao are processed. Here, the mybatis framework is used for processing. See the specific implementation of mapper:

		UPDATE user_resume SET
		<if test='resume.address!=null'>
		where userId=${userId} and resumeId=${resumeId}; 

   therefore, the entire resume update process is described above, because during SQL interaction, the resume table is maintained through update, and the rows to be updated are limited through userId and resumeId. Because the userId is bound to the user session, users can only update their corresponding resume to prevent the problem of exceeding their authority.

   however, there is a risk of SQL injection due to the use of $for annotation in Mybatis. Then further check whether there are relevant protective measures. It is found here that the relevant filter measures should be that the system injects too much, so the development uniformly processes the user input through the filter and checks the specific implementation of the filter.

  for the same filter, check the following points:

  • Filter order
  • Is the way of obtaining data comprehensive
  • Filtered rule content

   it is composed of two filters, which are respectively responsible for the input detection of xss and SQL injection. Because malicious input is directly detected and returned to the general error page, there is no filter bypass caused by sequence problems.

   the second is the way to obtain data, which is through multiparthttpservletrequest multireq = multipartresolver resolveMultipart(request); Convert the current file upload into an ordinary request object, and then conduct relevant input checks to prevent bypass caused by multipart submission:

	private CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver();
 	public void doFilter(ServletRequest req, ServletResponse res, FilterChain filterChain)
 			throws IOException, ServletException {
 		public static String[] MULTI_FILE_WHITE_LIST = new String[]{"/manager/uplode"};
 		HttpServletRequest request = (HttpServletRequest) req;
 		HttpServletResponse response = (HttpServletResponse) res;
 		String pathInfo = request.getPathInfo() == null ? "" : request.getPathInfo();
 		String url = request.getServletPath() + pathInfo;

 		String contentType = request.getContentType();// Get the content type of the request

 		//If it is a file upload interface, it does not need to be converted. It needs to directly check the input at the interface

 		// File upload request * special request
 		if (multipartResolver.isMultipart(request)) {
 			MultipartHttpServletRequest multiReq = multipartResolver.resolveMultipart(request);
 			request = multiReq;
 		//SQL injection check

   in addition, for the resume update interface, the submitted data type is in the normal key value form, so the method of obtaining data by filter here is comprehensive.

   finally, there are relevant filtering rules, which should be judged in combination with the database type. The system uses mysql database, and the filtering rules are as follows:

String badSqlStr ="'|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|sleep|order|by|count|*|chr|mid|master|truncate|char|declare|;|--|+|,|like|//|/|%|#";

   most SQL keywords are filtered, including select, which is necessary for SQL injection and drag content, and XP_ Sensitive SQL functions such as cmdshell. For the resume update interface, the SQL injection should be unable to obtain database sensitive information and other malicious operations at first glance.

   through the above analysis, the ultra vires and SQL injection problems of updating resume interface seem to have been alleviated temporarily. In fact, you can modify other people's resumes "beyond your authority" through SQL injection.

   the conditions for finding relevant update statements are where userId=${userId} and resumeId=${resumeId}, where userId is obtained from the current session and the user is not controllable, resumeId is transmitted from the front end and the user is controllable. In other words, when the resumeId passed by the current end is 1 or userId=2, you can update the resume content of the user with userId=2 while updating the resume of the current user, so as to achieve the effect of exceeding authority.

   the parameters submitted are as follows (because the or keyword is not filtered in the filter, the logic can bypass the filter security check):

address=xxxxx&{Relevant resume content}&resumeId=10001 or userId=2

   the SQL statement finally executed is roughly as follows:

UPDATE user_resume SET content='test',user_name='test'{Omit relevant contents} where userId = $userId and resumeId=$resumeId or userId=2;

    the specific effect is further explained here in combination with the database table. For convenience, the database content is simplified into the following structure: userId represents the user identity, resumeId represents the corresponding resume, and finally content represents the content of the resume:

   under normal circumstances, suppose you log in to the account with userId 1 and try to update the resume with resumeId 10000. The corresponding SQL statement is (here, change the content test to test111):

UPDATE user_resume SET content='test111' where userId = 1 and resumeId=10000;

  corresponding user after execution_ The content of the resume table changes as follows:

   according to the previous analysis, because there is SQL injection in resumeId and the user is controllable, try to submit resumeId=1 or userId=2, and try to modify the content of the user's resume with userId=2 to access control bypass without authorization. The corresponding SQL statements are as follows:

UPDATE user_resume SET content='Access-Control-Bypass' where userId = 1 AND resumeId=1 OR userId = 2;

  successfully used SQL injection to modify the resume information of other users beyond their authority:

   for the repair of SQL injection, it is not the best choice to check the user's input through filter / interceptor. When a specific scenario is met, it may lead to, for example, the above "ultra vires" problem.

   at the same time, many developers have misunderstandings that there will be no SQL problem after SQL interaction with JPA and HQL. The essence of SQL injection is to use string splicing to construct SQL statements when dealing with the interaction between application and database. At the same time, the relevant sensitivity parameters are controllable. As long as they are met, there is a risk of SQL injection. However, after using HQL, due to its characteristics, it is impossible to directly execute native SQL and write files, Execute commands and other operations, and do not support cross database table lookup and other sensitive operations. However, we can still try to achieve the above "ultra vires" harm in combination with the actual scene.

Posted by hubfub on Wed, 04 May 2022 23:48:35 +0300