Avoiding SQL injection in java

Question

I am new to JavaEE and trying to learn to make a simple login page by checking the database. Here is the code sample:

ResultSet result=null;
Statement s = (Statement) con.createStatement();
result=s.executeQuery("select username from Table where ID="+id and " password="+password);

It should be vulnerable to SQL injection right? I would do this by using parametrized query in ASP.NET like the following:

SqlConnection con = new SqlConnection();
SqlCommand cmd=new SqlCommand("select username from Table where ID=@id and password=@password",con);    
cmd.Parameters.AddWithValue("@id", id); 
cmd.Parameters.AddWithValue("@password", password);

Is there any way to use parametrized queries in Java like this? Can anyone use that query in parametrized form to avoid SQL injection?

@Ayman i have no idea about ORM or Hibernate, i m very new to JavaEE — yrazlik
– yrazlik, Commented Jun 30, 2013 at 15:08
@bigO It could definitely be worth the time to learn one if you will be doing this more than two times. — Ayman
– Ayman, Commented Jun 30, 2013 at 15:09

jwenting · Accepted Answer · 2013-07-01 13:29:31Z

4

Yes you can do this with PreparedStatement; for example:

PreparedStatement preparedStatement = con.PreparedStatement(
        "SELECT * FROM MY_TABLE WHERE condition1 = ? AND condition2 = ?");
preparedStatement.setString(1,condition1_value);
preparedStatement.setString(2,condition2_value);
ResultSet rs = preparedStatement.executeQuery();

edited Jul 1, 2013 at 13:29

jwenting

5,6593 gold badges28 silver badges33 bronze badges

answered Jun 30, 2013 at 15:37

Niju

4871 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stephen C Over a year ago

In fact, that query should be valid in any dialect of SQL.

fthiella · Accepted Answer · 2013-07-01 12:32:10Z

Here's a abridged version of a class dedicated to dynamically producing a query based on various criteria. The Query is constructed using JPA CriteriaBuilder API. You will need to adapt this code to your table structure. You should find this class easy to extend once you a understand the code.

@Stateless
public class sample {


    @PersistenceContext
    private EntityManager em;
    private CriteriaBuilder criteriaBuilder;
    private Root<Branch> root;
    private CriteriaQuery<Integer> query;


    @PostConstruct
    private void init(){
        criteriaBuilder = em.getCriteriaBuilder();
        resetCriteria();
        query = criteriaBuilder.createQuery(Integer.class);
        root = query.from(Branch.class);
    }


    private String branchName;
    private Predicate[] validPredicates;


    public void resetCriteria() {
        branchName = "";
        query = criteriaBuilder.createQuery(Integer.class);
        validPredicates = new Predicate[0];
    }




    public void setBranchName(String criteria){
        this.branchName = criteria;
    }




    public List<Integer> runQuery(){
        buildPredicates();

        if(validPredicates.length != 0){
            query.select(root.<Integer>get("branchID"))
                .where(criteriaBuilder.and(validPredicates));
        }
        else{
            query.select(root.<Integer>get("branchID"));
        }
        TypedQuery<Integer> q = em.createQuery(query);
        List<Integer> result = q.getResultList();
        return result;
    }




    private void buildPredicates(){
         ArrayList<Predicate> predicates = new ArrayList<Predicate>();

         if(branchName != null && !branchName.isEmpty()){
             StringBuilder sb =  new StringBuilder();
             sb.append("%")
                .append(branchName)
                .append("%");
            Predicate p = criteriaBuilder.like(root.<String>get("branchName"), sb.toString());
            predicates.add(p);
         }

         createPredicateArray(predicates);
     }  
     private void createPredicateArray(List<Predicate> predicates){
         if (predicates.size() > 0){
            validPredicates = new Predicate[predicates.size()];
            predicates.toArray(validPredicates);
         }
         else{
            validPredicates = new Predicate[0];
         }
     }


}

keerthana · Accepted Answer · 2019-02-28 19:59:56Z

You can use the sanitizer class;

*jdbcTemplate.execute(sanitizer.sanitize(sql), (PreparedStatementCallback<Integer>) preparedStatement -> {
preparedStatement.execute();*

Sanitizer class:

*import org.apache.commons.codec.DecoderException;
import org.apache.commons.codec.binary.Hex;
import org.springframework.data.domain.Sort.Direction;
public class Sanitizer {

     // Used to sanitize a path for fortify
    public String sanitize(String path) throws IllegalArgumentException {
        if (path == null) {
            return null;
        }
        String cleanString = "";
        for (int i = 0; i < path.length(); ++i) {
            cleanString += sanitizeChar(path.charAt(i));
        }
        return cleanString;
    }
    public byte[] sanitize(byte[] bytes) throws IOException, Exception, DecoderException {
        if (bytes == null) {
            return null;
        }
        String hex = Hex.encodeHexString(bytes);
        return Hex.decodeHex(sanitize(hex));
    }
    public Long sanitize(Long input) throws IllegalArgumentException {
        if (input == null) {
            return null;
        }
        if (input instanceof Long) {
            return input;
        } else {
            throw new IllegalArgumentException("Invalid input!");
        }
    }
    public Integer sanitize(Integer input) throws IllegalArgumentException {
        if (input == null) {
            return null;
        }
        if (input instanceof Integer) {
            return input;
        } else {
            throw new IllegalArgumentException("Invalid input!");
        }
    }
    public List<String> sanitize(List<String> inputList) throws IllegalArgumentException {
        List<String> list = new ArrayList<>();
        if (isNullOrEmpty(inputList)) {
            return list;
        }
        for (String input : inputList) {
            list.add(sanitize(input));
        }
        return list;
    }
    public Direction sanitize(Direction direction) {
        if (direction == null) {
            return null;
        }
        String sanitizedStr = direction.toString();
        return Direction.valueOf(sanitize(sanitizedStr));
    }

    private char sanitizeChar(char character) throws IllegalArgumentException {

        for (int i = 0; i < 128; ++i) {
            if (character == i) {
                return (char) i;
            }
        }
        switch (character) {
        case '.':
            return '.';
        case '_':
            return '_';
        case '-':
            return '-';
        case '?':
            return '?';
        case '/':
            return '/';
        case '=':
            return '=';
        case ':':
            return ':';
        case '\\':
            return '\\';
        case '@':
            return '@';
        case '&':
            return '&';
        case '%':
            return '%';
        case '+':
            return '+';
        case ',':
            return ',';
        case '#':
            return '#';
        case '(':
            return '(';
        case ')':
            return ')';
        case '{':
            return '{';
        case '}':
            return '}';
        case '$':
            return '$';
        case '*':
            return '*';
        case '<':
            return '<';
        case '>':
            return '>';
        case '!':
            return '!';
        case ' ':
            return ' ';
        case '~':
            return '~';
        case '^':
            return '^';
        case '`':
            return '`';
        case ';':
            return ';';
        case '[':
            return '[';
        case ']':
            return ']';
        case '|':
            return '|';
        case '"':
            return '"';
        case '\'':
            return '\'';
        case '  ':
            return '    ';
        }
        throw new IllegalArgumentException("Invalid character found:" + character + ":");
    }
        public static <T> boolean isNullOrEmpty(Collection<T> list) {
    if (list == null || list.size() == 0) {
        return true;
    }
    return false;*

This will solve the Sql injection when you are trying to get dynamic query on the fly. It will also save us from fortify scans.

Collectives™ on Stack Overflow

Avoiding SQL injection in java

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related