Hacker News Reader

Comment by Pxtl | Hacker News Reader

Never ask me to review or support any code that's using dynamic SQL written in MS T-Sql or Oracle. Not that dynamic SQL is inherently bad, but the procedural and string-management tools of those languages are appallingly awful and SQL is a bad tool for templating/smashing strings together.

Which, fundamentally, is what dynamic SQL is.

When I realized the article was going to be about that kind of dynamic SQL, I closed the tab.

Building SQL queries dynamically is fine.

Doing it within the SQL server is an abomination.

View on HN

Coming from an Oracle background, both application and data warehousing, I wholeheartedly agree with the articles first paragraph saying that application based dynamic SQL is horrible, but DBA (and I'll include data warehousing) dynamic SQL is great, but it's essentially about how complex the thing is.

A DBA is likely doing things like looping over metadata tables and running

  'grant select on ' || some_table || ' to ' || some_user

While an app developer with free reign will end up doing something much more complex, and much harder to reason about/tune

What about some_user has characters that need to be escaped?

The parent article touches on this point too - both Oracle and SQL Server (and any other major db) supports parameterising values in dynamic sql. some_user would become @some_user (SQL Server) or :some_user (Oracle).

(The characters never actually get escaped with parameterisation - they are not part of the query text when it is parsed so can’t affect it - hence parameterising a value in sql query replaces the need to escape it with something much more robust.)

It is also somewhat frustrating that bind variables cannot be used with DDL.

I am perfectly able to:

  SELECT ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME=:var;

But I cannot:

  ALTER USER :var ACCOUNT UNLOCK;

I really don't understand why that capability wasn't added a decade ago, as loud as the advice is to avoid hard parsing.

Yeah. This is a definite problem when working with user generated databases (eg untrusted), that could have pretty much anything in them. :/

That, and the lack of functions with a varying number of parameters, and lack of type polymorphism, and of easy to construct local views, and the lack of sum types...

Dynamic SQL is used on practice to fix a lot of problems the SQL people have been refusing to touch.

So this is about creating SQL strings with SQL? Not interpolating SQL with ? placeholders in say Python.

The article could have made that much more clear.

It reminds me of the bash-completion project which is the default on Debian-based distros.

It tries to parse bash IN BASH. This is a very bad idea.

Oil Uses Its Parser For History And Completion - https://www.oilshell.org/blog/2020/01/history-and-completion...

I would also argue that parsing bash in C is not a great idea either!

This, if you want to go the dynamic SQL route, please do it using some decent glue language or templating system that is not SQL itself. Unless you hate yourself that is.

For analytics work tools like dbt are really gaining traction for this reason.

Most of the OLAP / warehousing stuff I used to lean on dynamic sql for can be done more cleanly, with the benefit of easy source control.

https://www.getdbt.com/

Reply on news.ycombinator.com