Hacker News Reader

Comment by Animats | Hacker News Reader

> SQLite supports this syntax. But because of its TCL heritage, SQLite also allows the parameter to take the form of a TCL variable. Hence:

    SELECT passwd, photo FROM user WHERE uid=$uid

Did they put "eval" in SQL parameter processing? Is there an SQL injection attack vulnerability there?

View on HN

> Is there an SQL injection attack vulnerability there?

No, at least not if you put the SQL inside of {...}, which IIRC the documentation strongly recommends.

The $uid is passed down into SQLite. It is a single token recognized by the SQL parser itself. It does not get expanded by TCL. The $uid token serves the same roll as a "?" or ":abc" token would in some other SQL implementations. It is a placeholder for a value. The tclsqlite3.c interface first parses the SQL, then asks for the names of all of the placeholder tokens. Then it binds the values in TCL variables of the same name to those placeholders.

Indeed, this whole mechanism is specifically designed to make it easy to write SQL-injection-free code. As long as you put your SQL inside of {...}, you are completely safe from SQL injections.

If your TCL script includes SQL text inside of "...", then TCL will do the expansion and SQL injection is possible. But as long as the SQL text is inside of {...}, SQL injection is not possible.

Fully cognizant that I'm rolling the dice by responding to this comment, but isn't picking a variable syntax that could resolve in the unsafe way a "you're holding it wrong" waiting to happen?

  % set ex1 "SELECT * FROM FOO WHERE alpha=$bravo"
  can't read "bravo": no such variable
  % set ex2 "SELECT * FROM FOO WHERE alpha=?1"
  SELECT * FROM FOO WHERE alpha=?1

Don't get me wrong, https://peps.python.org/pep-0249/#paramstyle allowing %s and %(alpha)s are similar footguns and I wish they didn't exist, but at least they don't automatically resolve in the way that $ does in Tcl

It's an idiomatic Tcl thing. E.g. `expr` (the standard word used to evaluate infix expressions like `1+2`) does the same exact thing, handling the expansion itself and expecting the caller to use {} to ensure that a variable expansion not incorrectly treated as a bunch of operators. Similarly, when you're writing the condition for a loop, you need to use {} to delay expansion so that the loop word can do it anew for each iteration. One can argue that this is somewhat error prone, but at least there's a consistent pattern here, and once you know what it is and why it exists, it's pretty straightforward.

Reply on news.ycombinator.com