Issue with ArrayParameters.updateQueryWithArrayParameters when query contains '?' character

[rdigiorgio]

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);
    }

}