Issue with ArrayParameters.updateQueryWithArrayParameters when query contains '?' character #336
Description
Given the following Sql2o query:
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 (?)
- arrayParametersSortedAsc
[ArrayParameter{parameterIndex: 2, parameterCount: 2}]
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);
}
}