4paradigm / 4paradigm/OpenMLDB

Parameteried insert statement execute fail when we have `?` in string

Open
#285 3 comments 0 reactions 1 assignee Assigned to @tobegit3hub View on GitHub
bug python
Dominant language
C++
Stars
1.7k
Forks
331
Avg merge
12d 12h
Merged PRs (30d)
1

Description

Issue tracker is **ONLY** used for reporting bugs. New features should be discussed on our discussion

## Expected Behavior

## Current Behavior

```sql
Traceback (most recent call last):
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
File "/opt/rh/rh-python38/root/usr/local/lib/python3.8/site-packages/sqlalchemy_openmldb/openmldbapi/_openmldbapi.py", line 223, in execute
raise DatabaseError("parameters is not enough")
sqlalchemy_openmldb.openmldbapi._openmldbapi.DatabaseError: parameters is not enough

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "rtidb_client_test.py", line 49, in test_basic
connection.execute(insert2, ({"col4":"anhui", "col5":2}));
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1003, in execute
return self._execute_text(object_, multiparams, params)
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1172, in _execute_text
ret = self._execute_context(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1316, in _execute_context
self._handle_dbapi_exception(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1510, in _handle_dbapi_exception
util.raise_(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
raise exception
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
self.dialect.do_execute(
File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
cursor.execute(statement, parameters)
File "/opt/rh/rh-python38/root/usr/local/lib/python3.8/site-packages/sqlalchemy_openmldb/openmldbapi/_openmldbapi.py", line 223, in execute
raise DatabaseError("parameters is not enough")
sqlalchemy.exc.DatabaseError: (sqlalchemy_openmldb.openmldbapi._openmldbapi.DatabaseError) parameters is not enough
[SQL: insert into tsql1010 values(1001, '2020-12-26', 'hefei?', ?, ?);]
[parameters: {'col4': 'anhui', 'col5': 2}]
(Background on this error at: http://sqlalche.me/e/13/4xp6)
```
## Possible Solution

Fix the preprocess logic when execute SQL text in _openmldbapi.py.

## Steps to Reproduce

- build openmldb and sqlalchemy_openmldb
```
cmake -DSQL_PYSDK_ENABLE=ON -DSQL_JAVASDK_ENABLE=OFF -DTESTING_ENABLE=OFF ..
make sqlalchemy_openmldb openmldb
```

- add python sdk case :
```python
def test_basic(self):
engine = db.create_engine('openmldb:///db_test?zk=127.0.0.1:6181&zkPath=/onebox')
connection = engine.connect()
try:
connection.execute("create database db_test;")
except Exception as e:
pass
try:
connection.execute("drop table tsql1010;")
except Exception as e:
pass

time.sleep(2)

connection.execute(ddl)
insert2 = "insert into tsql1010 values(1001, '2020-12-26', 'hefei?', ?, ?);" # anhui 2
connection.execute(insert2, ({"col4":"anhui", "col5":2}));

```

- execute test
```
sh steps/fesql_test_python.sh
```
## Context (Environment)

## Detailed Description

## Possible Implementation

Contributor guide

Open the contributing guide

Research direction

The bug is in sqlalchemy_openmldb/openmldbapi/_openmldb.py where SQL text preprocessing incorrectly counts '?' inside string literals as parameter placeholders. Start by examining the execute method in that file, trace how parameters are matched. Write a test that reproduces the error with a string containing '?', then adjust the preprocessing logic to ignore '?' within quotes. Verify by running the provided test steps.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql, sqlalchemy
Domain
backend, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.