Failure to escape query parameters in parentheses

[Apreche]

NOTE: I first reported this bug to psycopg2 believing it was more likely to be a problem on their end. I was told to report it here instead. I will say that I have not attempted to test for this bug using any other SQL client libraries besides psycopg2. If it is a problem with the fdw, more testing using other types of clients will be necessary.

https://github.com/psycopg/psycopg/issues/513

Summary

When making a query to an SQLite foreign table that has multiple parameters inside of a set of parentheses, those parameters will fail to be escaped properly, causing errors, and potentially allowing for SQL injection. This only happens in a very specific circumstance, so I have provided many example queries to illustrate the issue as clearly as possible.

I tested this bug using the latest version of this docker image https://hub.docker.com/r/toleg/postgres_sqlite_fdw toleg/postgres_sqlite_fdw:latest as of 2023-02-19.

Python version 3.11.2 psycopg2 version 2.9.5 sqlite verison 3.34.1

Steps to reproduce

1. Create an SQLite database. In that database create a table with a text column named title.
2. Create a PostgreSQL database with an identical table. This is not needed to reproduce the bug. It is just to demonstrate the difference in behavior.
3. Insert several rows of data into both tables.
4. Connect to the PostgreSQL database and setup the foreign data wrapper so that the SQLite database can be queried via fdw.

   CREATE EXTENSION IF NOT EXISTS sqlite_fdw;
   CREATE SERVER IF NOT EXISTS sqlite_source FOREIGN DATA WRAPPER sqlite_fdw OPTIONS (database '/path/to/sqlite.db');
   CREATE SCHEMA IF NOT EXISTS fsqlschema;
   IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_source INTO fsqlschema;

5. Verify that the foreign table can be successfully queried.

   SELECT * from fsqlschema.test_table;

6. Run the following Python code to verify that psycopg2 is correctly querying local and foreign tables.

   import psycopg2
   connection = psycopg2.connect("connection stuff here")
   cursor = connection.cursor()
   cursor.execute("select * from public.test_table;")
   cursor.execute("select * from fsqlschema.test_table;")

7. All of the following queries will work and have their parameters escaped properly. These queries will work correctly on both the normal table and the foreign table.

   # One parameter
   cursor.execute("select * from fsqlschema.test_table where title = %s;", ["Tests"])
   # One parameter that needs escaping
   cursor.execute("select * from fsqlschema.test_table where title = %s;", ["Test's"])
   # Multiple parameters that may or may not need escaping
   cursor.execute("select * from fsqlschema.test_table where title = %s or title = %s;", ["Test", "Tests"])
   cursor.execute("select * from fsqlschema.test_table where title = %s or title = %s;", ["Test's", "Tests"])
   # One parameter inside of parentheses that may or may not need escaping
   cursor.execute("select * from fsqlschema.test_table where title in (%s);", ["Tests"])
   cursor.execute("select * from fsqlschema.test_table where title in (%s);", ["Test's"])
   # Multiple parameters inside of parentheses that don't need escaping
   cursor.execute("select * from fsqlschema.test_table where title in (%s, %s);", ["Tests", "other tests"])

8. This is the bad query. This query will work just fine against the normal table. It has multiple parameters in parentheses and at least one of them has a quote that needs escaping.

   cursor.execute("select * from public.test_table where title in (%s, %s);", ["Test's", "other tests"])

9. The same exact query when targeted at the foreign table will raise an error.

   cursor.execute("select * from fsqlschema.test_table where title in (%s, %s);", ["Test's", "other tests"])

   Traceback (most recent call last):
   File "/workspace/bugtest.py", line 5, in <module>
   cursor.execute("select * from fsqlschema.test_table where title in (%s, %s);", ["Test's", "other tests"])
   psycopg2.errors.FdwUnableToCreateExecution: SQL error during prepare: near "s": syntax error SELECT `id`, `title` FROM main."test_table" WHERE (`title` IN ('Test's', 'other tests'))

   When the exact same query with the exact same parameters was sent to the normal table, the quote in the parameter was escaped and the query executed correctly. When the query is sent to the foreign table, the quote is not escaped properly, and the query fails to execute.

Expected Behavior

The query in step 9 should work exactly the same as the query in step 8. The quote in the query parameter should be properly escaped regardless of whether the table in the query is foreign or not.

[mkgrgis]

There is uncontrolled string transformation near https://github.com/pgspider/sqlite_fdw/blob/75183518ffc1b6d7be942a6b99136bb36e0fc9d8/deparse.c#L2601 in sqlite_deparse_const.

[t-kataym]

@Apreche Thank you for reporting. I can reproduce the issue on psql.

In case of comparison operator, string is escaped correctly. But in case of IN condition, the escape procedure is not worked. (IN condition with a single value is converted to a comparison operator. So one parameter case was no problem.)

We will fix it.

Temporary, you might be able to fix it by appending the following code before the line 2943 in deparse.c.

https://github.com/pgspider/sqlite_fdw/blob/master/deparse.c#L2943

if (SQL_STR_DOUBLE(ch, true))  // New
    appendStringInfoChar(buf, ch); // New
appendStringInfoChar(buf, ch); // 2493

[t-kataym]

@Apreche This issue was fixed by https://github.com/pgspider/sqlite_fdw/commit/9fd31f43b08ca64006b86d0de316222c213d0081

[t-kataym]

@Apreche , If no problem, could you close this issue?