Using prepared statement in the most secure way

Question

From a security validation perspective, is there a difference between:

stmt.setObject(1, theObject);

and

stmt.setString(1, theObject);?

I know that in this case theObject is a String but I am interested in making part of this code more general to cover other cases and was wondering if the security perspective of input validation is affected

score 1 · Answer 1 · answered Nov 05 '12 at 08:47

1

It is ok to use ssetObject() because jdbc will try to to do the type resolution for all java.lang.* types.

However, there is potential problem with passing an arbitrary SQL string to the database in this way - Security loopholes: without very judicious validation of any parameters that you use to build up the SQL string, you are liable to various types of SQL insertion attacks.

Beware of passing untyped null to setObject()

answered Nov 05 '12 at 08:47

aviad

8,229
9
50
98

Is the passing of an SQL String an issue also for `setString`? – Jim Nov 05 '12 at 09:59
yes. You need to do some sandbox checking before executing the statement. – aviad Nov 05 '12 at 10:59
This doesn't sound correct.I have read that it is recommended to use `PreparedStatements` for proper input.You are saying *I* am responsible to "parse" the sql strings beforehand? – Jim Nov 05 '12 at 11:42
What I meant was that you sometimes need to check that the parameters you use as arguments for your prepared statements do not contain `malicious code AKA SQL injection' – aviad Nov 05 '12 at 13:33
So to avoid SQL injection I should use something else on top? – Jim Nov 05 '12 at 14:46
Sure. For example if you get the parameters from web form you must check them. In case you build the parameters in other piece of your code the check is redundant. – aviad Nov 05 '12 at 16:29

SJuan76 · Answer 2 · 2012-11-05T08:45:03.220

0

IMHO

Given that JDBC is a very light wrapper around the database server (it makes little else than generating the SQL for the DB to directly interpret), I expect

stmt.setObject(1, theObject);

to be exactly the same than

stmt.setString(1, theObject == null ? "null" : theObject.toString())`;

The "type validation" will happen when the database processes the resulting SQL and finds if it fits it.

edited Nov 05 '12 at 08:45

answered Nov 05 '12 at 08:37

SJuan76

24,532
6
47
87

2

JDBC and ODBC are two different technologies and JDBC is not a wrap around ODBC (although a bridge exists). – Paolo Nov 05 '12 at 08:44
@Paolo I know, I was short of an expression at the time. I changed it for `database engine` which is a better fit. – SJuan76 Nov 05 '12 at 08:46
But if this is all how is input validation enforced?Not properly escapped strings etc? – Jim Nov 05 '12 at 10:33
It is enforced **after** that step. – SJuan76 Nov 05 '12 at 10:51

score 0 · Answer 3 · answered Nov 05 '12 at 09:05

The answer seems to be provider related and depends of the implementation of the driver. I check the source of the current postgresql driver and there the two calls are equal.

If the driver don't know the type a exception is thrown.

/** code from ./org/postgresql/jdbc2/AbstractJdbc2Statement.java */
public void setObject(int parameterIndex, Object x) throws SQLException
{
    checkClosed();
    if (x == null)
        setNull(parameterIndex, Types.OTHER);
    else if (x instanceof String)
        setString(parameterIndex, (String)x);
    else if (x instanceof BigDecimal)
        setBigDecimal(parameterIndex, (BigDecimal)x);
    else if (x instanceof Short)
        setShort(parameterIndex, ((Short)x).shortValue());
    else if (x instanceof Integer)
        setInt(parameterIndex, ((Integer)x).intValue());
    else if (x instanceof Long)
        setLong(parameterIndex, ((Long)x).longValue());
    else if (x instanceof Float)
        setFloat(parameterIndex, ((Float)x).floatValue());
    else if (x instanceof Double)
        setDouble(parameterIndex, ((Double)x).doubleValue());
    else if (x instanceof byte[])
        setBytes(parameterIndex, (byte[])x);
    else if (x instanceof java.sql.Date)
        setDate(parameterIndex, (java.sql.Date)x);
    else if (x instanceof Time)
        setTime(parameterIndex, (Time)x);
    else if (x instanceof Timestamp)
        setTimestamp(parameterIndex, (Timestamp)x);
    else if (x instanceof Boolean)
        setBoolean(parameterIndex, ((Boolean)x).booleanValue());
    else if (x instanceof Byte)
        setByte(parameterIndex, ((Byte)x).byteValue());
    else if (x instanceof Blob)
        setBlob(parameterIndex, (Blob)x);
    else if (x instanceof Clob)
        setClob(parameterIndex, (Clob)x);
    else if (x instanceof Array)
        setArray(parameterIndex, (Array)x);
    else if (x instanceof PGobject)
        setPGobject(parameterIndex, (PGobject)x);
    else if (x instanceof Character)
        setString(parameterIndex, ((Character)x).toString());
    else if (x instanceof Map)
        setMap(parameterIndex, (Map)x);
    else
    {
        // Can't infer a type.
        throw new PSQLException(GT.tr("Can''t infer the SQL type to use for an instance of {0}. Use setObject() with an explicit Types value to specify the type to use.", x.getClass().getName()), PSQLState.INVALID_PARAMETER_TYPE);
    }
}

If this is how it is being done, how is the input validation enforced? — Jim, Nov 05 '12 at 10:30
@Jim What other kind of validation do you want? For instance sql injection can only work with strings but only if it is not right "escaped". I hope this job do the jdbc provider right :-D. On other types the cast will crash. — OkieOth, Nov 05 '12 at 11:46
@Jim every time you read about SQL Injection experts suggest to use prepared statement. IMO the reason is the deeper handling of prepared parameters - they are set only as values to the database. No compiler or stuff is run to interpret them. But it was an interesting question. Next time I will dive a bit deeper into the JDBC source code. Btw like the source code shows the untyped null is no problem for the postgres jdbc driver. — OkieOth, Nov 05 '12 at 20:41
@Jim years ago I visited a lecture about implementing database system a the university. What we learn was a normal sql statement is handled in the following steps from database: 1. syntax checks 2. optimizer 3. compiler prepares the statement for execution If you use prepared statements the first 3 steps are done and bad code in parameters has no chance to be compiled or interpreted - excepting you can use buffer overflows and stuff. So if databases work today in same way, should there be no way for sql injection over this way. — OkieOth, Nov 05 '12 at 21:06
@Jim looks here http://stackoverflow.com/questions/8263371/how-prepared-statements-can-protect-from-sql-injection-attacks first answer shares my point of view — OkieOth, Nov 05 '12 at 21:14

Using prepared statement in the most secure way

3 Answers3