Support passing parameters to SQL query

[1Dragoon]

Is it possible to send prepared statements to MSSQL? I'm able to do this with tiberius directly, but I don't see a way to do it with connectorx. Also I don't see a way to use integrated authentication in windows environments, which can also be done with the bare tiberius crate, is that also possible? EDIT: figured out how to get integrated auth, just don't specify username/password in the url

Just using Rust BTW, not python.

[wangxiaoying]

Hi @1Dragoon , thanks for brining up this issue! We don't support prepared statements for now. I think the difficulty here is that we need to require the input parameters' type implement the ToSql trait of all the databases. Since we support multiple databases here and each driver defines their own ToSql or equivalent trait. May I ask the performance difference between using prepared statement and not using it in your scenario? Also please kindly me know if you find an easy way to tackle the issue.

For windows integrated authentication, you can add the trusted_connection=true parameter in the uri like mssql://host:port/db?trusted_connection=true.

[1Dragoon]

Sorry for not following up for a long time, this isn't really for a performance concern so much as it is a security concern. Basically the use case here is being able to easily delineate between user defined SQL code and developer defined SQL code to i.e. mitigate SQL injection.

[ghilesmeddour]

Hi 👋,

Thanks @wangxiaoying for this lib, its performance really shines <3

This feature would be very useful. I guess what's needed is something similar to params of pandas.read_sql. (PEP249)

[char101]

For postgresql at least we can emulate it client side

import psycopg2
import connectorx as cx

DSN = 'postgresql://username:password@host:port/dbname'

conn = None

def read_sql(sql, params=None):
    global conn
    if params:
        if conn is None:
            conn = psycopg2.connect(DSN)
        sql = conn.cursor().mogrify(sql, params).decode('utf-8')
    return cx.read_sql(DSN, sql)

[david-waterworth]

FYI it's now

from psycopg import connect, ClientCursor

with connect(DSN, cursor_factory=ClientCursor) as cn:
    sql = cn.cursor().mogrify(sql, params)

For psycopg3

[yasaslive]

Any update on this issue? I'm experimenting with connectorx and need to pass some params without injections. Is there any other ways we can use?