Let me start off by saying that I am not a DBA. Everything you are about to read may not be doable at all.
SQL Injection sucks. If you’ve ever successfully been hit by an attack, it’s like getting kicked in the stomach. Even with the best of intentions, things can sometimes happen.
Back to the point of this post. Here’s what I think the big DB maintainers could do. It’s not an easy path, many people have to play along nicely to make it feasible.
From my understanding, once an attackable script is found, the attacker has to do a bunch of queries to get a little bit of data. And it usually starts with querying the database for the available tables. Then describing the tables to get the field names, etc…
And also from my understanding, my code doesn’t need this information for every page load. Sure, sometimes I may need access to it, for example to automate building a model from an existing table. But for my everyday use, I think I could get away with my scripts having to know what the tables are, and what the structure is.
What if the DB engine itself, didn’t allow you to ask for the list of databases, or tables within the database without a private key? As long as you don’t connect to the DB supplying this data, you’d be good to go. Anyone asking for a list of tables would be locked out.
This is the hard part. All of our tools for DB management would have to play along. I’m a personal fan of Sequel Pro on my Mac. They would have to have a way to supply the key for me so that tools like this could function. They routinely have to ask for a list of databases and tables.
In the mean time, I will just have to try and get around this by filtering in any of my code that does querying to look for these types of queries and report them to me ASAP to let me determine if they are good. And decide if this is something I could just BLOCK altogether in the future.
Would love to hear the opinions of real DBAs or people that know more about this stuff than I do.