Comment by zachrip
2 months ago
That's a good point, has anyone hardened a database by locking out users who select columns that don't exist? Or run other dubious queries? This would obviously interrupt production but if someone is running queries on your db it's probably worth it?
I once did an security assessment for a product such as what you describe. Among other problems with it, the product itself had SQL injection vulnerabilities
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.
If you are mature enough to do that, you're mature enough to net SQL injections in the first place. There shouldn't be that many handwritten queries to review in the first place as most mundane DB access is usually through a framework that handles injection properly...
I disagree, if all it took was maturity then we wouldn't see giant data breaches of the largest companies in the world weekly.
Zane Lackey (with Dan Kaminsky) gave a talk that discussed doing literally that sort of things, back in 2013. Zane went on to found Signal Sciences (acquired by Fastly), doing this sort of stuff in the 'WAF' space.
https://youtu.be/jQblKuMuS0Y?t=866 (timestamp is when Zane starts talking about it)
I guess the main difference is that a WAF attempts to spot things like injection (unbalanced delimiters, SQL keywords in HTTP payloads where SQL shouldn't exist, etc.) typically without knowledge of the schema, whereas GP is talking about the DBMS spotting queries where queries must exist but disagree with the schema. Might as well do both, I suppose.
That’s not what the talk is about - it’s using dbms query error logs to spot attackers. Stuff like “table doesn’t exist” or “invalid syntax” on your production database can be extremely high signal indications that something is wrong, potentially maliciously so.
In the very early 2000’s I worked at a company building something along those lines. We could analyze SQL and SMB traffic on the fly and spot anomalous access to tables/columns/files, etc. Dynamic firewalling would have been the next progression if the company didn’t have other issues.
WAFs help with this, but at the HTTP level. By putting “information_schema”, “sys.tables” in the filters.
Not the real solution, IMO, but WAFs are useful for more than SQLi, and is the kind of tech you can ask money for.
On the surface that’s a very attractive idea.
A sort of “you shouldn’t be in here, even if we left the door unlocked.”
So if you deploy code before you run the associated db migration, or misspell a column name, you magnify the impact from whichever code paths (& application tier nodes) are running the broken SQL, to your entire production environment.
Simple variation to a hard shutoff: immediately page "significant risk a successful sql exploit was found", and then slow down attackers:
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.
Yeah it's definitely something that could do more harm than good to a company long term. But I'm sure there are instances where this tradeoff is worth it. They would invest more heavily in runbooks or maybe even ci that runs migrations on deploy. Deleting columns would need to be done on your deploy + 1. Probably no rollback at all.