sql2o is a small library, which makes it easy to convert the result of your sql-statements into objects. No resultset hacking required. Kind of like an orm, but without the sql-generation capabilities. Supports named parameters.
SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100')
FROM table WHERE foo = :foo AND bar in (:bar)
When building the prepared statement, Sql2o calls static method ArrayParameters.updateQueryWithArrayParameters after having replaced named parameters by '?' characters.
Lets say I set 2 values in my bar named parameters, we will call the static method with parameters:
parsedQuery
SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100')
FROM table WHERE foo = ? AND bar in (?)
As the method iterates through the parsed query and searches for '?' characters to try and add the '?' character 1 more time into the parsed query, it count the first '?' from my CONCAT('http://phatdomain.com/', path, '?width=100') statement and then fails to add '?' placeholder at the right place, giving the resulting parsed query:
SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table
WHERE foo = ?,? AND bar in (?)
Here is a simple test case to reproduce the issue:
package org.sql2o;
import com.google.common.collect.ImmutableList;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
/**
* Created by romain on 04/12/2019
*/
public class SimpleTest {
@Test
public void works() {
String query = "SELECT name, CONCAT('http://phatdomain.com/', path) FROM table WHERE foo = ? AND bar in (?)";
ArrayParameters.ArrayParameter barArrayParameter = new ArrayParameters.ArrayParameter(2, 2);
query = ArrayParameters.updateQueryWithArrayParameters(query, ImmutableList.of(barArrayParameter));
Assertions.assertEquals("SELECT name, CONCAT('http://phatdomain.com/', path) FROM table WHERE foo = ? AND bar in (?,?)", query);
}
@Test
public void doesNotWork() {
String query = "SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ? AND bar in (?)";
ArrayParameters.ArrayParameter barArrayParameter = new ArrayParameters.ArrayParameter(2, 2);
query = ArrayParameters.updateQueryWithArrayParameters(query, ImmutableList.of(barArrayParameter));
Assertions.assertEquals("SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ? AND bar in (?,?)", query);
}
}
Given the following Sql2o query:
When building the prepared statement, Sql2o calls static method
ArrayParameters.updateQueryWithArrayParameters
after having replaced named parameters by '?' characters.Lets say I set 2 values in my
bar
named parameters, we will call the static method with parameters:As the method iterates through the parsed query and searches for '?' characters to try and add the '?' character 1 more time into the parsed query, it count the first '?' from my
CONCAT('http://phatdomain.com/', path, '?width=100')
statement and then fails to add '?' placeholder at the right place, giving the resulting parsed query:Here is a simple test case to reproduce the issue: