Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot. And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful.
You can "always" do that? Well I just did that. My database said: no such table: information_schema.columns
And what if my database had disabled this capability entirely?
Also, is there anything implying SQL here at all? Can't other databases with injection "capability" have schemas?
> Plus, generally if you have SQL injection, you have multiple tries. You're not going to be locked out after one shot.
No, you can't say it with such certainty at all. It really depends on what else you're triggering in the process of that SQL injection. You could easily be triggering something (like a password reset, a payment transaction...) where you're severely limited in your attempts.
> And there's only so many combinations of `SELECT {id,userid,user_id,uid} FROM {user,users,login,logins,customer,customer}` before you find something useful.
account, accounts, password, passwords, profile, profiles, credential, credentials, auth, auths, authentication, authentications, authentication_info, authentication_infos, authorization, authorizations, passwd, passwds, user_info, user_infos, login_info, login_infos, account_info, account_infos... should I keep going?
And these are just the logins/passwords; what if the information of interest was something else, like parking tickets?
Don't expect attackers to give up after one try. It depends on the database software, not everyone implements this exact ANSI standard for reflection but every database supports reflection. That's why the first step after finding a SQLi is to fingerprint the database software and go from there.
> And what if my database had disabled this capability entirely?
You can't disable it, lots of software, database features, ORMs and clients rely on reflection. If a client can query a table they also can retrieve metadata about that table.
Not even going into reasonability of ORMs, most of the stuff I've seen or implemented added practically 0 added value, and added hard-to-debug issues down the line as software evolved. Cargo culting at its best, often done on trivial schemas that could handle either direct SQL or some sql-query-to-object mapping easily.
- A: guaranteed SQL-injection-proof (SQL injection impossible.) - B: Having non-obvious table-names and 'secure-defaults' (e.g. INFORMATIONSCHEMA disabled).
So, the original commenter says, he wants to _hide the schema_, so that B can protect him in case of A. Well, failure of A is Amateur Hour. If you fail on A, I highly doubt you would have delivered correctly on B. To write it out in plain text: If you have set up and manage an application with SQL injection errors, I have a hard time seeing you still taking care to disable /enable obscure security defaults, or take care to avoid obvious and trivial table names.
Just to put icing on the cake: As soon as you have an SQL injection attack, a simple select * from randomTable or DESC randomTable would give you the table COLUMNS, so it utterly makes no sense to want to hide those column names - you have already lost them! (in the case you are arguing you need their protection in). ..Unless you argue that the guy making sql injection applications ALSO has set up a secure default to disallow select *..
In my experience, SQL injection is evidence of work of the sloppiest and immature nature; it was bad in 2003, and presumably still is.
1: you can set secure defaults at one place globally, but your code must be correct all the time to be free of SQLi
2: it's usually not the same persons who configure the DB and who write the code.
Security is an onion, not a coconut.
For another example of what defenders are up against, see https://users.ece.cmu.edu/~adrian/731-sp04/readings/Ptacek-N.... This paper all but caused an upheaval in the WAF industry.
https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
A sort of “you shouldn’t be in here, even if we left the door unlocked.”
If an SQL query requests an unknown table, log the error, but have that query time out instead of responding with an error. Or, even better, the offending query appears to succeed, but returns fake table data, turning it into a honeypot built-in to the DB. This could be done at the application layer, or in the DB.
The goal is to buy an hour for defenders to determine how to respond, or if its a red herring. There are a variety of ways of doing this without significant user impact.