Failing to escape single backtick in SQL values

[lochana-chathura]

Description:
$subject.

Steps to reproduce:
Try executing this code.

import ballerina/sql;
import ballerinax/java.jdbc;

type Payment record {|
    string date;
    decimal amount;
    int employee_id;
    string reason;
|};

public function main() returns error? {
    Payment[] paymentArr = [{date: "2022-02-03", amount: 0.0, employee_id: 0, reason: "`"}];

    string jdbcUrl = "jdbc:h2:" + "./db/gofigure";
    jdbc:Client dbClient = check new (jdbcUrl, "root", "root");

    sql:ParameterizedQuery[] insertQuaries = from var payment in paymentArr
        select `INSERT INTO Payment(date, amount, employee_id, reason) VALUES(${payment.date}, ${payment.amount}, ${payment.employee_id}, ${payment.reason})`;

    sql:ExecutionResult[] batchResult = check dbClient->batchExecute(insertQuaries);
    check dbClient.close();
}

It gives the following error:

error: Error while executing batch command starting with: 'INSERT INTO Payment(date, amount, employee_id, reason) VALUES( ? ,  ? ,  ? , `)'. Syntax error in SQL statement "INSERT INTO Payment(date, amount, employee_id, reason) VALUES( ? ,  ? ,  ? , [*]`)"; SQL statement:
INSERT INTO Payment(date, amount, employee_id, reason) VALUES( ? ,  ? ,  ? , `) [42000-210]. {"errorCode":42000,"sqlState":"42000"}

The issue is caused by the reason: "`" in the first line of the main function.

Affected Versions:
2201.5.2

Related Issues:
#2056

[lochana-chathura]

Both @HindujaB and I were able reproduce the issue. We started off with 2201.8.1 and there it was working. Then we went down with the ballerina version.

At 5.2 I was able reproduce the issue. The details of the sql and DB in the Dependencies.toml as follows.

[[package]]
org = "ballerina"
name = "sql"
version = "1.9.0"
dependencies = [
	{org = "ballerina", name = "io"},
	{org = "ballerina", name = "jballerina.java"},
	{org = "ballerina", name = "lang.object"},
	{org = "ballerina", name = "time"}
]
modules = [
	{org = "ballerina", packageName = "sql", moduleName = "sql"}
]

org = "ballerinax"
name = "java.jdbc"
version = "1.9.0"
dependencies = [
	{org = "ballerina", name = "io"},
	{org = "ballerina", name = "jballerina.java"},
	{org = "ballerina", name = "lang.string"},
	{org = "ballerina", name = "sql"},
	{org = "ballerina", name = "time"}
]
modules = [
	{org = "ballerinax", packageName = "java.jdbc", moduleName = "java.jdbc"}
]

However, after I reproduced it in 5.2, this is reproducible in 8.1 also. I tried deleting Dependencies.toml and also by executing bal clean. Now, it is reproducible in 8.1 with sql version 1.11.0 and java.jdbc version 1.11.0.

[lochana-chathura]

@niveathika is looking into this further, she discovered the fix done for the original issue(#2056) is incomplete. She will send a patch reverting the original fix.

ETA for the fix will be coming Monday.