check input for SQL-Injection with Mybatis

Question

I'd like to check my input string for potential SQL-Injection.

Here is my class, method and query:

public class UserNamesQuery {

   public static String getUserNames(Map<String, Object> params) {
       String userNames = (String) params.get("userNames");
       return "SELECT * FROM users WHERE name IN (" + userNames + ") ";
   }

}

Is there a tool or a quick way to validate that userNames is without SQL-Injection?

Notice that I use Mybatis

You probably want to use a PreparedStatement instead of reinventing the wheel. — Mena
– Mena, Commented Mar 7, 2016 at 12:02
Prepared statements is good advice, but this in an IN value list. PreparedStatement with list of parameters in a IN clause — Alex K.
– Alex K., Commented Mar 7, 2016 at 12:09

Your Common Sense · Accepted Answer · 2016-03-07 12:18:49Z

8

No. There is no way. And no need.

To be frank, there is no such thing like "SQL injection". There is only an exploit of improperly formatted query.

So, instead of hunting down whatever "injections" you have to format your queries properly, by means of using prepared statements.

Any data, depends on context, could be either a potential injection or a harmless chunk of text. So, with whatever filtering function there will be too much false positives. Worse yet, whatever filtering is a "black list" implementation, means it will always be incomplete - it's just impossible to filter out all the codes used to exploit an injection.

On the other hand, prepared statement is a relatively simple solution that will be immune to any type of injection without even knowing them. Just because it won't let the data to interfere with the query.

edited Mar 7, 2016 at 12:18

answered Mar 7, 2016 at 12:03

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

5 Comments

Igor Over a year ago

How would you purpose to overcome the IN in my query, and how can I apply it to mybatis?

Florian Schaetz Over a year ago

And no problem with MyBatis there, since you can give the parameters as #{parameterName} which will be automatically translated into a prepared statement. For your IN clause, you typically use a foreach statement in the MyBatis mapping.

Igor Over a year ago

@Florian Schaetz how do I do that exactly? maybe you'll write it in a separate answer

Florian Schaetz Over a year ago

To answer that question completely, I would have to reproduce the whole MyBatis documentation. Start with mybatis.org/mybatis-3/getting-started.html , then have a look at mybatis.org/mybatis-3/dynamic-sql.html#foreach - If there are more specific questions open after you tried that, simply ask them in a new question, since YCS' answer is completely correct for this one.

Florian Schaetz Over a year ago

Just a little addition: Especially important for you is the chapter about parameters mybatis.org/mybatis-3/sqlmap-xml.html#Parameters and especially String substitutions mybatis.org/mybatis-3/sqlmap-xml.html#String_Substitution (in short: Don't use them for user input, use parameters instead - and as an addition: NEVER, EVER build your query with user input. ). This article might also help somewhat: software-security.sans.org/developer-how-to/…

Sven Olderaan · Accepted Answer · 2016-07-26 10:57:09Z

0

Sanitizing input is not the way to prevent injections like this. Using prepared statements is the way to go.

PreparedStatement ps = connection.prepareStatement("SELECT * FROM users WHERE username IN (?)"); //Add however many ?'s you want, if you have an array you can use a StringBuilder for this to add more ?'s
ps.setString(1, userName);
ResultSet rs = ps.executeQuery();

This will set the ? in the code to your string. The database driver then handles the rest. If you have multiple values in the IN clause, use a StringBuilder and a loop to add more Questionmarks.

Also notice how the indexing starts with 1 instead of 0.

answered Jul 26, 2016 at 10:57

Sven Olderaan

705 bronze badges

Comments

Mr.H · Accepted Answer · 2019-08-08 13:39:51Z

-1

mybatis sql template may be a good choose. FYI:

<sql id="orderTypeSql">
    <trim prefix=" ">
        <if test="orderType=='desc'">desc</if>
    </trim>
</sql>

<sql id="oderColumnSql">
    <trim prefix="order by " suffix="" >
        <choose>
            <when test="orderColumn==null or orderColumn==''"></when>
            <when test="orderColumn=='id'">
                id<include refid="orderTypeSql"/>
            </when>
            <when test="orderColumn=='name'">
                `name`<include refid="orderTypeSql"/>
            </when>
        </choose>
    </trim>
</sql>

<select id="testOrderBy" resultType="User">
    select
    id,
    `name`
    from t_user
    <include refid="oderColumnSql"/>
    limit 0, 10
</select>

edited Aug 8, 2019 at 13:39

answered Aug 8, 2019 at 13:12

Mr.H

13 bronze badges

Collectives™ on Stack Overflow

check input for SQL-Injection with Mybatis

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related