6 ms·

> 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

by _jhqp 2y ago

> 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.

EGreg 2y ago
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 2y ago
  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 2y ago
    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 2y ago
      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.
magicalhippo 2y ago
> 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/ https://xkcd.com/327/
- lmz 2y ago
  "Gee, this new programming language / API makes it hard to copy my SQL queries across. Better use something else."
  - magicalhippo 2y ago
    If that's all what the datanase drivers supported...

by _jhqp 2y ago

EGreg 2y ago

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 2y ago
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 2y ago
  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 2y ago
    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.

magicalhippo 2y ago

> 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/ https://xkcd.com/327/

lmz 2y ago
"Gee, this new programming language / API makes it hard to copy my SQL queries across. Better use something else."
- magicalhippo 2y ago
  If that's all what the datanase drivers supported...