Hacker News Reader

Comment by gruez | Hacker News Reader

>If your DB library is inserting the parameters in a text query before sending it off to the server to be parsed as arbitrary SQL, that's not a parametrized query, but just fake smoke and mirrors.

Do you have examples of libraries that's actually a "parametrized query"? The python libraries I encountered (psycopg2, sqlalchemy) both seem to be "fake smoke and mirrors".

View on HN

This is one of the major differences between psycopg2 and psycopg3 - https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html#se...

Active Record from Ruby on Rails does. It’s actually a problem on older versions because it may incorrectly generate duplicates of queries that could use the same prepared statement but don’t. Can cause unexpected memory usage.

If ADO.NET counts as a library for you: Parameterized queries for MS SQL through ADO.NET.

They are seemingly run through a stored procedure on the server side, with each parameter passed in as an argument. This has some consequences that lead to very obscure behavior, too [1].

For example, you should never create temporary tables in an SQL statement that was initialized with parameters, as they won't survive the end o the statement; they will be destroyed as soon as the innermost scope (within the stored procedure call) finishes.

I ran into this because I tried setting up a single "run this query" method in a more complicated database routine in order to keep my C# code clean... didn't work out as I'd hoped ;)

[1]: https://stackoverflow.com/a/46311328

Edit: I just realized that this still isn't a guarantee that parameters are handled as a separate structure by the underlying network protocol. I hope they are -- but I don't know how to check. Maybe the according .NET Core code is on GitHub?

Just run SQL Server Profiler. You can see what is hitting your server, it's a very useful tool.

MySqlConnector (C# - https://github.com/mysql-net/MySqlConnector) uses "smoke and mirrors" for the "text" protocol (because MySQL Server has no way to send parameters as part of a `COM_QUERY` packet, https://dev.mysql.com/doc/internals/en/com-query.html) and true parameters for the "binary" protocol, which does (https://dev.mysql.com/doc/internals/en/com-stmt-execute.html).

Every MySQL client needs to do something similar, due to the design of the MySQL protocol.

Hibernate has actual prepared statements and binds values when the query is executed.

sqlite3 con.execute() appears to prepare and execute a statement, with bound parameters.

Reply on news.ycombinator.com