CODEWITHSUNDEEP
javascriptjavat-sql
Aeglasin
Aeglasin

Reputation: 165

prepared statement / sql-injection preventation on variable from

I read about that prepare statments are a good way to avoid sql injections to databases. the problem is the Customer wants a quiet variable UI where he first selects a table, then some contraints consisting of a column and a text. So basically the (naive) endproduct will look like this:

Select * from %TABLENAME% where %ATTRIBUTENAME% = %VALUE%

Now the question is how to get this secure?

I could, of course, build a prepare Statement solution where I create statements for all tables in advance, but that sounds to me like a pretty stupid idea, because the effort to maintain this would be quiet big (the customer has quiet a few tables). Any idea how to solve this in a secure manner that is as generic as possible?

Upvotes: 0

Views: 48

Answers (1)

lance-java
lance-java

Reputation: 27994

You should change your example to

select * from %TABLENAME% where %ATTRIBUTENAME% = ?

So that at least the VALUE can't be used for SQL injection. You could then have validation for TABLENAME and ATTRIBUTENAME against the known tables and columns in your database.

See DatabaseMetaData.getColumns(...) which you might use in your validation at runtime. Or perhaps you might keep a static file, generated at build time, with the valid tables/columns.

You could generate an Enum at build time for every table/column combination? I know jOOQ does this sort of build time java code generation from a db schema... perhaps it can help?

Eg

public enum TableColumn {
    CUSTOMER_NAME("customer", "name"), CUSTOMER_ID("customer", "id"),
    ORDER_ID("order", "id"), // etc etc

    String table;
    String column;

    public TableColumn(String table, String column) {
        // set values
    }
}

public List<Row> doSelect(TableColumn tc, Object value) {
    String sql = String.format("select * from %s where %s = ?", tc.table, tc.column);
    Connection con = getConnection();
    try {
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setObject(1, value);
        ...

Upvotes: 1

Related Questions