JDBI如何在防止SQL注入的同时动态创建WHERE子句？ [英] JDBI How can I dynamically create a WHERE clause while preventing SQL Injection?

问题描述

我想动态过滤JDBI查询。

I want to dynamically filter a JDBI query.

参数列表通过REST从UI传递，例如

The a list of parameters is passed from the UI via REST e.g.

http://localhost/things?foo=bar&baz=taz
http://localhost/things?foo=buz

这是（笨拙地）建立（泽西@Context UriInfo :: getQueryParameters - > StringBuilder）这样的事情：

Which is (clumsily) built (Jersey @Context UriInfo::getQueryParameters -> StringBuilder) to something like this:

WHERE foo=bar AND baz=taz

并传递给JDBI，如下所示：

And passed to JDBI which looks like this:

@UseStringTemplate3StatementLocator
public interface ThingDAO {
   @SqlQuery("SELECT * FROM things <where>)
   List<Thing> findThingsWhere(@Define("where") String where);
}

据我所知，当前的实现很容易受到SQL注入。
我显然可以清理列名而不是值。 1

As far as I understand the current implementation is vulnerable to SQL injection. I can obviously sanitize the column names but not the values. 1

必须有更优雅的SQL注入证明方法。

There must be a more elegant and SQL Injection proof way of doing this.

推荐答案

受到Jean-Bernard的启发我想出了这个：

public class WhereClause {
    public HashMap<String, String> queryValues; // [<"foo","bar">, <"baz","taz">]
    public String preparedString; // "WHERE foo=:foo AND bar=:baz"
}

这是通过自定义Binder绑定 BindWhereClause ：

Which is bound via a custom Binder BindWhereClause:

@BindingAnnotation(BindWhereClause.WhereClauseBinderFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindWhereClause {
    class WhereClauseBinderFactory implements BinderFactory {
        public Binder build(Annotation annotation) {
            return new Binder<BindWhereClause, WhereClause>() {
                public void bind(SQLStatement q, BindWhereClause bind, WhereClause clause) {
                    clause.queryValues
                            .keySet()
                            .forEach(s -> q.bind(s, clause.queryValues.get(s)));
                }
            };
        }
    }
}

以及<$的组合c $ c> @Define 和 @Bind ：

@UseStringTemplate3StatementLocator
public interface ThingDAO {
   @SqlQuery("SELECT * FROM things <where>")
   List<Thing> findThingsWhere(@Define("where") String where, 
                               @BindWhereClause() WhereClause whereClause);
}

这应该是注射证明。 （是吗？）

This should be injection proof. (is it?)

这篇关于JDBI如何在防止SQL注入的同时动态创建WHERE子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！