Suppose I am doing a server API that accepts and processes full-fledged sql database requests, and not just stored procedures. To ensure security, I have a list of blocked commands (all but select, insert, update, and delete). Assuming that all the string data in the query is safe, I simply cut it out of the query that came to the server so that they do not interfere with the search for dangerous commands. Repex cut lines:

/(\'|").*[^\\]\1/

Is it possible to somehow make a sql query so that string data harms the database?

PS To avoid misunderstandings, by "string data" I mean data that is written inside string literals with ' or " . For example in this query: select * from users where name="Vasya" , "Vasya" is string data.

  • if I understood correctly? but what's stopping you from writing что-то to select from что-то where a = a; for example - Dmitriy
  • in general, procedures are created to get rid of direct requests to the database, plus in the procedure, you can once again check the data, and if something went wrong, roll back the transaction - Dmitriy
  • @Dmitriy select can be written, no one will interfere with this. The drop command, for example, cannot be written, for this my script will be banned. But the question is not about that at all. - Diskyp
  • The query with a condition true (type where a = a, or where 1 = 1) will be fulfilled? - Dmitriy
  • @Dmitriy yes, it will. - Diskyp

1 answer 1

String literals themselves are always safe. But you have a regular regular! The string in double quotes in sql is not a string literal, but an identifier ... And how many more such errors does the cryptic script contain?

It is much better if you use the capabilities of the DBMS: you will get a separate limited user and will perform other people's requests on his behalf.

  • To be honest, it was possible to limit the first sentence and somehow back it up. Thanks for participating. - Diskyp