Named placeholders for SQL

[johnfoldager]

I have a string representing a SQL statement with placeholders (:name-of-placeholder) like:

SELECT * FROM table WHERE id = :id AND name = :name AND description LIKE "%:something%"

Is it possible to process it so that I can get each placeholder (:id and :name, but not :something) and replace each with a value (numeric or string), such that:

• numerics will be inserted in-place without quotes,
• strings will be inserted in-place with quotes around but in such a way so that if string is fx ' OR TRUE' then ti should escape it correct in order to prevent SQL injection.

[manticore-projects]

Greetings!

Yes, this is certainly possible, however I wonder if you are not looking for a more sophisticated approach:

1. identify all named parameters
2. replace with ? placeholder for un-named parameters
3. create a map between every named parameter and its position of un-named parameters
4. provide an utility function, which fills the values of the named parameters for each un-named parameter using setObject( position, value)

Rationale: this approach allows for using Prepared Statements and Batch Updates yielding in much better performance instead of parsing single statements with hard coded parameters under heavy performance penalty.

In a nutshell, you would call a methods:

1. ResultSet rs = getResultSet( String sqlStr, Map<String, Object> parameters) or
2. long n = executeUpdate(String sqlStr, Map<String, Object> parameters), which will depend on a Map/Pool of Prepared Statements identified by the SQL ID

This is not necessary in Scope of JSQLParser, but rather belongs into a JDBC extension.

[johnfoldager]

The solution to call a method with the SQL string and a map of parameters is what I need, however, I haven't been able to figure out how to do this the correct way. I've already tried the following for named queries:

• NamedPreparedStatement from https://www.infoworld.com/article/2077706/named-parameters-for-preparedstatement.html
• NamedParameterStatement from https://www.codemeright.com/blog/post/named-parameterized-query-java

but both fail on SQL injection problems. This is why I searched and found JSqlParser which might be able to actually find these :parameters and replace with a value and at the same time know how to escape quotes etc correctly in order to prevent SQL injection.

To me it looks like something like https://github.com/JSQLParser/JSqlParser/wiki/Examples-of-SQL-building--part-2 could potentially be used for this, however, I haven't worked with JSqlParser before, so wanted to know if this is possible at all.

[johnfoldager]

By the way.... forgot to say that the SQL comes from a configuration file and is made by another system based on different stuff, so I have to somehow parse SQL correctly, identify these :placeholders and replace based on some values that I have.

[manticore-projects]

Yes, clear. I will send you a few lines of code tomorrow.

[manticore-projects]

Greetings.

A first commit: https://github.com/manticore-projects/MJdbcUtils
Have a look at the Unit Tests, they illustrate how to use it.

I will add more stuff over the next couple of days.

[johnfoldager]

I don't agree ;-)

I'm setting a parameter to a string which is put into the SQL statement without being properly escaped. As I wrote I can escape the parameter before putting it into the SQL. The problem is that the parameter value itself can change the SQL resulting in a different AST. However, if each string parameters was escaped correctly then it would result in the same AST no matter if that parameter did or did not try SQL injection.

[johnfoldager]

I might be wrong, and I'm going to look deeper into it tomorrow. For now I can replace string apostrophes with double ones to prevent SQL injection.

[manticore-projects]

Can you please give me the final SQL, properly formatted and with syntax highlighting? Just as a plain SQL without any Java code? I guess I got confused and saw a SQL Injection, which you actually want to avoid.

[manticore-projects]

I think, I completely misunderstood your request, sorry for that.
I have added an Oracle DB compliant escaping for ' and &. Give it a try please.

[johnfoldager]

Now your code works as expected which indicates to me that JSqlParser with some add-on code can be used for my little project. However, since your code is GPLv3 licenses I don't think I can use it in my project.

[manticore-projects]

I have pushed support for PreparedStatements, which will effectively avoid SQL injection:

CREATE TABLE test ( 
a DECIMAL(3) PRIMARY KEY
, b VARCHAR(128) NOT NULL
, c DATE NOT NULL
, d TIMESTAMP NOT NULL
, e DECIMAL(23,5) NOT NULL 
) 

String ddlStr = "INSERT INTO test VALUES ( :a, :b, :c, :d, :e )";
String qryStr = "SELECT Count(*) FROM test WHERE a = :a or b = :b";

Map<String, Object> parameters = toMap("a", 1, "b", "Test String", "c", new Date(), "d", new Date(), "e", "0.12345");

// Insert data
MPreparedStatement st = new MPreparedStatement(conn, ddlStr);
st.execute(parameters);

// Query data
MPreparedStatement st = new MPreparedStatement(conn, qryStr);
ResultSet rs = st.executeQuery(parameters);

[manticore-projects]

Added support for Batch execution:

                MPreparedStatement st = new MPreparedStatement(conn, ddlStr, batchSize);

                for (int i=0; i < maxRecords; i++) {
                    parameters.put("a", i);
                    parameters.put("b", "Test String " + i);
                    
                    // effectively executed only when BATCH_SIZE is reached
                    int[] results = st.addAndExecuteBatch(parameters);
                }
                // effectively executed only when there are uncommitted records left
                int[] results = st.executeBatch();