Ask HN: Is there is an sqlite3_expanded_SQL()'s equivalent for other DBMS?

6 pointslfconsult2y ago9 comments

Hi HN,

I'm digging around in order to find a way to get back the SQL statement from a compiled statement with bound parameters (avoid SQLi) for others DBMS.

I've found - with excitements- that the SQLite C interface have a sqlite3_expanded_sql()* function to get exactly what I needed.

As I didn't found anything for SQLServer, I thought - at first - that it was just not possible at all because the SQL statement and parameters were sent separately (or, kind of, maybe the same compiled blob but without any chance to building back the raw SQL statement).

For example, the raw SQL statement is : SELECT * FROM Genre WHERE Name = ?

The bound parameter is : "Pop"

I would like to get back : SELECT * FROM Genre WHERE Name = 'Pop'

Maybe it's a profane question but I did spend a lot of time around in order to find what I need.

If you ever wonder why I'm asking this question, just for curiosity. In order to know if it's not even thinkable, doable, or just no implemented yet or if it depends of the DBMS or 42.

* https://sqlite.org/c3ref/expanded_sql.html

9 comments

rawgabbit2y ago

Not exactly what you are looking for. But SQL server has several ways to track queries. One is DMV. The other is the Query Store.

https://blog.sqlauthority.com/2010/05/14/sql-server-find-mos...

https://learn.microsoft.com/en-us/sql/relational-databases/p...

lfconsultOP2y ago

Exact, it's not what I'm looking for but it's interesting anyway, thank you for your answer and the links.

It's a bit weird but I'm using a langage (for a specific project) where prepared statements aren't available (except if you pay or use a specific DBMS, I can't do both because the client won't pay and I need SQLServer).

My initial idea was to use a DLL from PDO (I'm developping on a Windows env) or whatever known piece of code that is trustable in order to sanitize user inputs (of course we do it client side but user is devil :-)) before sending it to SQLServer.

After all answers and my researches, I think that I'll use a tiers piece of code to escape queries (probably a C# program using SQLCommand).

Thanks all.

If anybody knows exactly how SQLServer is handling SQL Statements, please let us know.

lfconsultOP2y ago

Side note: The result of sqlite3_expanded_sql() is a pointer to the string containing the SQL statement with bound parametrer(s):

"The string returned by sqlite3_expanded_sql(P) [...] is obtained from sqlite3_malloc() [...]"

From SQLite documentation: https://www.sqlite.org/c3ref/expanded_sql.html

selecsosi2y ago

Not for sqlserver, but for postgres working w/ python, the psycopg library has a API for getting the bound parameter expression

https://www.psycopg.org/docs/cursor.html#cursor.mogrify

Most of this I would expect to be more on the client side than server (though w/ sqlite you are working w/ both)

lfconsultOP2y ago

Thank you for your answer.

I'm still wondering why it's not available for SQLServer (because there are use cases for this feature).

It is "just" a matter of driver interface (I mean the piece of code that implement ODBC API) that "just" do not give this feature or it's letteraly not possible with SQLServer?

Someone2y ago

I think it’s likely that other databases do not keep the query string you sent it around but convert it into some internal structure.

Converting that back to the query string you sent may be impossible because that structure won’t record comments, line breaks, indents, escaped characters and the like.

Converting it to a query string that, when sent back, produces the same internal structure should be possible, but as user selecsosi wrote “Most of this I would expect to be more on the client side than server”.

1 more reply

yellow_lead2y ago

MariaDB doesn't have one, I've searched before.

lfconsultOP2y ago

Thank you for your answer.

j / k navigate · click thread line to collapse

Ask HN: Is there is an sqlite3_expanded_SQL()'s equivalent for other DBMS?

6 pointslfconsult2y ago9 comments

Hi HN,

I'm digging around in order to find a way to get back the SQL statement from a compiled statement with bound parameters (avoid SQLi) for others DBMS.

I've found - with excitements- that the SQLite C interface have a sqlite3_expanded_sql()* function to get exactly what I needed.

For example, the raw SQL statement is : SELECT * FROM Genre WHERE Name = ?

The bound parameter is : "Pop"

I would like to get back : SELECT * FROM Genre WHERE Name = 'Pop'

Maybe it's a profane question but I did spend a lot of time around in order to find what I need.

If you ever wonder why I'm asking this question, just for curiosity. In order to know if it's not even thinkable, doable, or just no implemented yet or if it depends of the DBMS or 42.

* https://sqlite.org/c3ref/expanded_sql.html

9 comments

rawgabbit2y ago

Not exactly what you are looking for. But SQL server has several ways to track queries. One is DMV. The other is the Query Store.

https://blog.sqlauthority.com/2010/05/14/sql-server-find-mos...

https://learn.microsoft.com/en-us/sql/relational-databases/p...

lfconsultOP2y ago

Exact, it's not what I'm looking for but it's interesting anyway, thank you for your answer and the links.

After all answers and my researches, I think that I'll use a tiers piece of code to escape queries (probably a C# program using SQLCommand).

Thanks all.

If anybody knows exactly how SQLServer is handling SQL Statements, please let us know.

lfconsultOP2y ago

Side note: The result of sqlite3_expanded_sql() is a pointer to the string containing the SQL statement with bound parametrer(s):

"The string returned by sqlite3_expanded_sql(P) [...] is obtained from sqlite3_malloc() [...]"

From SQLite documentation: https://www.sqlite.org/c3ref/expanded_sql.html

selecsosi2y ago

Not for sqlserver, but for postgres working w/ python, the psycopg library has a API for getting the bound parameter expression

https://www.psycopg.org/docs/cursor.html#cursor.mogrify

Most of this I would expect to be more on the client side than server (though w/ sqlite you are working w/ both)

lfconsultOP2y ago

Thank you for your answer.

I'm still wondering why it's not available for SQLServer (because there are use cases for this feature).

It is "just" a matter of driver interface (I mean the piece of code that implement ODBC API) that "just" do not give this feature or it's letteraly not possible with SQLServer?

Someone2y ago

I think it’s likely that other databases do not keep the query string you sent it around but convert it into some internal structure.

Converting that back to the query string you sent may be impossible because that structure won’t record comments, line breaks, indents, escaped characters and the like.

1 more reply

yellow_lead2y ago

MariaDB doesn't have one, I've searched before.

lfconsultOP2y ago

Thank you for your answer.

j / k navigate · click thread line to collapse