[_jhqp]

> why the SQL API even allows injection vulnerability

How would one implement this?

"SQL APIs" use prepared statements. Meaning you have a string for SQL and some dynamic variables that inject into that string via $1, $2 etc.

BUT now if developer makes that string dynamic via a variable, then you have SQL injection again.

[magicalhippo]

> How would one implement this?

The low-level API could simply not allow SQL statements as strings, and instead provide separate functions to build the queries and statements.

It would provide entry points which could be used to ensure proper escaping and such, and would still allow for easily generating queries dynamically in the cases where that is needed.

Of course, it doesn't completely guard against Bobby Tables[1], one could imagine someone including a run-time code generator and feed it unprotected SQL as input.

But it should make it a lot more difficult, as it would be much more "unnatural", requiring going against the grain, to inject unprotected user data. Also, the "query_execute" function could raise an error if there's more than one statement, requiring one to use a different function for batch execution.

Pseudo-codish example off the top of my head, for the sake of illustration:

   is_active = str_to_bool(args['active']); // from user
   qry = new_query(ctx);
   users_alias = new_table_alias(qry, 't');
   query_select_column(users_alias, 'id');
   query_select_column(users_alias, 'username');
   query_from_table(users_alias, 'users');
   filter_active = query_column_eq_clause(users_alias, 'active', is_active);
   where = query_where(qry);
   query_where_append(where, filter_active);   
   cursor = query_execute(qry);

[1]: https://xkcd.com/327/

[lmz]

"Gee, this new programming language / API makes it hard to copy my SQL queries across. Better use something else."

[magicalhippo]

If that's all what the datanase drivers supported...

[EGreg]

Easy. Don’t write queries in a language (SQL) which interpolates content without escaping it for the enclosing structure.

Go one level up.

For example statements that are prepared should not allow strings in the SQL, but rather variables, and then bind them to values like PDO does

[scotty79]

It would be a bit annoying to have to prepare outside and pass in every SQL literal you need to use in your query.

I'd rather have SQL API taking not strings but a special type that string can't be directly converted into without escaping (by default).

In C++ tagged literals could be used to create this special type easily. Similar constructs exist in some other languages

[EGreg]

Literally a library can generate SQL statements and compile them

JS and PHP has tagged literals

But they have to be “escaped” properly before being interpolated!

[scotty79]

That's the whole point of having a separate type for queries. Whenever you try to glue a string to a query the string gets escaped.