Custom Annotation in Java for SQL Injection Safe Parameters

How to verify if incoming data strings are safe from SQL Injection issues.

Ramakrishna Punjal

Updated Mar. 03, 17 · Tutorial

Likes (20)

Comment

Save

43.9K Views

Hi Java Developers, In the today’s world of ORM, we are mostly using JPA based implementations for our persistence.

We also use JPA over Hibernate, Spring Data, or Spring JDBC templates quite often.

While these frameworks already address the issue of SQL Injection to a great extent, we do run into certain scenarios where you still want to verify if the incoming data string is SQL-Injection safe.

Just in case someone wonders what SQL-Injection is, you can look it up here for a minimalistic introduction : http://www.w3schools.com/sql/sql_injection.asp

We should remember that using even the best of the tools won’t help if they are used in the wrong manner.

What I mean is that you should never construct your database queries along with the values (in case you are crafting them yourselves). E.g: select name, area from city where name = ‘Oslo’ and country = ‘Norway’

You should instead always ensure that your implementation uses prepared statements and parameters, e.g: select name, area from city where name = :cityName and country = :countryNameThis example is showing the usage of a named paramter, which could also be a just :? in case you are just using position based parameters. This is pretty safe with regards to SQL-Injection.

Coming back to our discussion, If you still want to verify if your incoming parameter parameters are SQL-Injection safe, one of the approaches you could take, is creating a custom annotation for this.

This is the approach that I had taken. I am sharing it with you and hope it helps you guys.

I used it primarily for a spring based rest-inspired web application.

So my example project will be using spring boot.

So let’s get our hands dirty.

The Annotation

Provided you have a simple java project, the first thing would be to define the annotation interface SQLInjectionSafe

import javax.validation.Constraint;
import javax.validation.Payload;
import java.lang.annotation.*;

@Documented
@Constraint(validatedBy = SQLInjectionSafeConstraintValidator.class)
@Target( { ElementType.FIELD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface SQLInjectionSafe {

    String message() default "{SQLInjectionSafe}";

    Class<?>[] groups() default {};

    Class<? extends Payload>[] payload() default {};

}

This is pretty straight forward. You define the annotation name and the targeted section of the code.

You should note the @Target( { ElementType.FIELD, ElementType.PARAMETER})

This would help you use your annotation over Fields and parameters, the most likely place for incoming data.

And you also specify the validator for the data. SQLInjectionSafeConstraintValidator.class

We take a look at the validator in the next section.

The Validator

In the Annotation interface we had specified the validator : @Constraint(validatedBy = SQLInjectionSafeConstraintValidator.class)

Now let’s create the validator itself which is the brain of our SQL-Injection safe annotation.

import javax.validation.ConstraintValidator;
import javax.validation.ConstraintValidatorContext;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLInjectionSafeConstraintValidator implements ConstraintValidator<SQLInjectionSafe, String> {


    public static final String SQL_TYPES =
            "TABLE, TABLESPACE, PROCEDURE, FUNCTION, TRIGGER, KEY, VIEW, MATERIALIZED VIEW, LIBRARY" +
            "DATABASE LINK, DBLINK, INDEX, CONSTRAINT, TRIGGER, USER, SCHEMA, DATABASE, PLUGGABLE DATABASE, BUCKET, " +
            "CLUSTER, COMMENT, SYNONYM, TYPE, JAVA, SESSION, ROLE, PACKAGE, PACKAGE BODY, OPERATOR" +
            "SEQUENCE, RESTORE POINT, PFILE, CLASS, CURSOR, OBJECT, RULE, USER, DATASET, DATASTORE, " +
            "COLUMN, FIELD, OPERATOR";

    private static final String[] sqlRegexps = {
            "(?i)(.*)(\\b)+SELECT(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+INSERT(\\b)+\\s.*(\\b)+INTO(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UPDATE(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DELETE(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UPSERT(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+SAVEPOINT(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+CALL(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+ROLLBACK(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+KILL(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+CREATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+ALTER(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+TRUNCATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+LOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+UNLOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+RELEASE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
            "(?i)(.*)(\\b)+DESC(\\b)+(\\w)*\\s.*(.*)",
            "(?i)(.*)(\\b)+DESCRIBE(\\b)+(\\w)*\\s.*(.*)",
            "(.*)(/\\*|\\*/|;){1,}(.*)",
            "(.*)(-){2,}(.*)",

    };

    // pre-build the Pattern objects for faster validation
    private List<Pattern> validationPatterns = getValidationPatterns();

    @Override
    public void initialize(SQLInjectionSafe sqlInjectionSafe) { }

    @Override
    public boolean isValid(String dataString, ConstraintValidatorContext cxt) {
        return isSqlInjectionSafe(dataString);
    }

    private boolean isSqlInjectionSafe(String dataString){
        if(isEmpty(dataString)){
            return true;
        }

        for(Pattern pattern : validationPatterns){
            if(matches(pattern, dataString)){
                return false;
            }
        }
        return true;
    }

    private boolean matches(Pattern pattern, String dataString){
        Matcher matcher = pattern.matcher(dataString);
        return matcher.matches();
    }

    private static List<Pattern> getValidationPatterns(){
        List<Pattern> patterns = new ArrayList<Pattern>();
        for(String sqlExpression : sqlRegexps){
            patterns.add(getPattern(sqlExpression));
        }
        return patterns;
    }


    private static Pattern getPattern(String regEx){
        return Pattern.compile(regEx, Pattern.CASE_INSENSITIVE | Pattern.UNICODE_CASE);
    }

    private boolean isEmpty(CharSequence cs) {
        return cs == null || cs.length() == 0;
    }

}

In the method isValid, we deligate the verification to another method isSqlInjectionSafe, and this is where the magic happens.

First we have a guard check on the incoming dataString is null safe. To do this, I could have used the commons StringUtils.isEmpty().

However, I would like to keep my annotations as self-dependent as possible. So, we have our own method isEmpty, which is pretty similar to the apache-commons isEmpty implementation.

Then we run our dataString through a series of Regular Expressions to determine if it matches any standard pattern of an SQL malicious data. In the first set of regular expressions we verify if it matches with any of these simple SQL patterns:

"(?i)(.*)(\\b)+SELECT(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+INSERT(\\b)+\\s.*(\\b)+INTO(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+UPDATE(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+DELETE(\\b)+\\s.*(\\b)+FROM(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+UPSERT(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+SAVEPOINT(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+CALL(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+ROLLBACK(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+KILL(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+DROP(\\b)+\\s.*(.*)",

The next set of expressions are:

"(?i)(.*)(\\b)+CREATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+ALTER(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+TRUNCATE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+LOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+UNLOCK(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",
"(?i)(.*)(\\b)+RELEASE(\\b)+(\\s)*(" + SQL_TYPES.replaceAll(",", "|") + ")(\\b)+\\s.*(.*)",

These regular expressions are tied to SQL_TYPES. It ensures that you do not create, alter, trucate, lock, release, or describe sql-types like tables, views, triggers, etc. The full list of sql-types secured come from this constant SQL_TYPES

I have tried to create as holistic list of sql-types, as possible from my side.

Next is the final set of expressions to detect disruptive characters in SQL:

"(?i)(.*)(\\b)+DESC(\\b)+(\\w)*\\s.*(.*)",
"(?i)(.*)(\\b)+DESCRIBE(\\b)+(\\w)*\\s.*(.*)",
"(.*)(/\\*|\\*/|;){1,}(.*)",
"(.*)(-){2,}(.*)",

Should you need, you could expand the SQL_TYPES and the set of regular-expressions even further.

Also one more thing to note is that we are pre-building the Pattern objects. Usually creating the Pattern object is the more time-consuming part. Now that we are already constructing those, we can expect a slight improvement in the performance (very slight improvement, something is better than nothing ;-) ).

The Usage

Now you can annotate your fields and parameters with @SQLInjectionSafe

Eg.

private @SQLInjectionSafe String id;

I have primarily used it in a spring mvc controller to validate the incoming request parameter.

Unfortunately, you cannot directly annotate a RequestParameter with our annotation and expect it work. Spring MVC has it’s own way of running validators on RequestParameters.

Refer to these links for this scenario:

Hence why directly annotating a request parameter does not work. The way you get this to work is to create a wrapper class and in that wrapper class annotate our parameter. So you could create a wrapper class that looks something like this:

public static class IdWrapper{

    private @SQLInjectionSafe String id;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }
}

Then you can use the wrapper in your controller like this:

@RequestMapping(value = "/getById)
public MyResponseObject getById(
        @Valid @ModelAttribute() IdWrapper idWrapper){

// do your stuff
}

Now when you have a validation Failure on the incoming parameter, Spring throws a BindException. If not handeled, the BindException is sent across as response directly.

To send a cleaner response, you can create an ExceptionHandler method in the controller.

This could be something like this :

@ExceptionHandler(BindException.class)
public @ResponseBody WebResponse handleBindException(BindException be ){

    return new MyResponseObject(false,
            getBindExceptionMessage(be) // find and send an appropriate response 
    );

}

Please note that this way of validating request parameters in controllers is just one of the many possible ways of doing it. It is just that I found this approach more convenient and clean among the other approaches.

You can run the web application by running Application.java. You can check what responses you get when you get these URLs:

http://localhost:3000//api/data/getById?id=123

http://localhost:3000//api/data/getById?id=create table abcd

Testing It

Since we are implementing something of significant importance, we better back it up with unit tests. I have done that and you will find it in the SQLInjectSafeConstraintValidatorTest.java source code on GitHub.

What we have tested with is the most common occurances of SQL embedded in regular data:

String[] maliciousDataSamples = {
        "select adf from abc",
        "insert into abcd",
        "update abcd",
        "delete from abcd",
        "upsert abcd",
        "call abcd",
        "rollback ",
        "create table abc",
        "drop table",
        "drop view",
        "alter table abc",
        "truncate table abc",
        "desc abc",
};

We have also tested some SQL-disruptive data like:

String[] sqlDisruptiveDataSamples = {
        "--",
        "/*",
        "*/",
        ";",
        "someone -- abcd",
        "abcd /* adf */ adf",
};

We have thoroughly verified if these malicious data samples are rejected when they are:

Individual
Prefixed and suffixed by something
All CAPS
Small letters

We should let good data to pass through. We verified that as well in the good data unit tests

You can find the sample project of this on GitHub here.

URL: https://github.com/rkpunjal/sql-safe-annotation-example

Include this directly as library/dependency into your project.

I have converted this into an open-source library which can be used in your project by including this as a dependency

Include this in your pom.xml to add the library as a dependency

<dependency>
  <groupId>com.github.rkpunjal.sqlsafe</groupId>
  <artifactId>sql-injection-safe</artifactId>
  <version>1.0.2</version>
</dependency>

The GitHub Link to the Library is: https://github.com/rkpunjal/sql-injection-safe/

And Now the Necessary Formalities

I believe this approach is pretty effective and worked well for me.

***My disclaimer is that you should use it at your own risk after evaluating if this solution is good for you.

I sincerely hope this helps you. Do comment in with your opinions.

Links

Annotation sql Java (programming language) Data (computing) Injection

Opinions expressed by DZone contributors are their own.

Related

Trending