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, Mar 07 '16 at 12:02
Prepared statements is good advice, but this in an `IN` value list. [PreparedStatement with list of parameters in a IN clause](http://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause) — Alex K., Mar 07 '16 at 12:09

Your Common Sense · Accepted Answer · 2016-03-07T12:18:49.787

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 07 '16 at 12:18

answered Mar 07 '16 at 12:03

Your Common Sense

156,878
40
214
345

How would you purpose to overcome the IN in my query, and how can I apply it to mybatis? – Igor Mar 07 '16 at 12:12
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. – Florian Schaetz Mar 07 '16 at 12:47
1

@Florian Schaetz how do I do that exactly? maybe you'll write it in a separate answer – Igor Mar 07 '16 at 12:50
1

To answer that question completely, I would have to reproduce the whole MyBatis documentation. Start with http://www.mybatis.org/mybatis-3/getting-started.html , then have a look at http://www.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 Mar 07 '16 at 13:01
Just a little addition: Especially important for you is the chapter about parameters http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Parameters and especially String substitutions http://www.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: http://software-security.sans.org/developer-how-to/fix-sql-injection-in-java-mybatis – Florian Schaetz Mar 07 '16 at 15:06

score 0 · Answer 2 · answered Jul 26 '16 at 10:57

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.

Mr.H · Answer 3 · 2019-08-08T13:39:51.540

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>

check input for SQL-Injection with Mybatis

3 Answers3