There was another malicious attack targeting websites running Microsoft Internet Information Server (IIS) recently. I would imagine that the vast majority of website developers and DBAs are aware of SQL Injection attacks and how to avoid them - primarily, by using nothing but parameterised queries in your code - and a large percentage of those have probably seen the excellent XKCD comic strip that illustrates (yeah, yeah, pun intended) the point nicely.
It never hurts to have a belt-and-braces approach to these things though, which is why version 9.0 of our Postgres Plus Standard Server and version 8.4 of Advanced Server will include a new module called SQL/Protect. This is a relatively small plugin to Postgres prototyped by Jim Mlodgenski, the Chief Architect here at EnterpriseDB which helps protect against these sort of attacks. It uses a number of techniques, such as detection of tautology in WHERE clauses of your SQL to try to detect and prevent injection attacks. It can also run in a learning mode in which it will monitor your web app and learn the usage patterns of the database so it can prevent anything out of the ordinary being executed.
Look out for it in our upcoming releases.
It never hurts to have a belt-and-braces approach to these things though, which is why version 9.0 of our Postgres Plus Standard Server and version 8.4 of Advanced Server will include a new module called SQL/Protect. This is a relatively small plugin to Postgres prototyped by Jim Mlodgenski, the Chief Architect here at EnterpriseDB which helps protect against these sort of attacks. It uses a number of techniques, such as detection of tautology in WHERE clauses of your SQL to try to detect and prevent injection attacks. It can also run in a learning mode in which it will monitor your web app and learn the usage patterns of the database so it can prevent anything out of the ordinary being executed.
Look out for it in our upcoming releases.
I think there is a need to go back to the drawing board to prevent SQL injection. Or at least we need to properly adapt prepared statements so that they are actually optimized to deal with SQL injection protection, since that was not their main original intend: http://pooteeweet.org/blog/1775
ReplyDeleteProper database-level permissions also goes a long way. Parameterized queries help, but often the real problem is that people (and their ORMs) love to connect to the database as a superuser *at all times*, even for operations that should inherently be low-privileged. The Principle of Least Privilege applies to more than merely avoiding running under Windows as Administrator all the time. It applies to all security.
ReplyDeleteA highly restricted database-level user has little or no opportunity to cause any real damage, even if they were given free-reign to write any arbitrary SQL they wished. They would simply not have the rights on the database to succeed.
well with privileges you can prevent DDL sql injection pretty well. then again this can easily be prevented by disallowing multiple statements to be send at once.
ReplyDeleterow level privileges are not really feasible in modern setups where there are simply way to many individual users with personal data. its simply not feasible to create individual db users with row level permissions.
so in other words .. no db privileges do not even come close to address the issue at hand.
that being said, it still makes sense to not have your normal app user have large sweeping DDL permissions.
@lsmith
ReplyDelete"Database-level permissions" is not one and the same as "row-level permissions". The point is that any database security strategy requires use of database-level permissions in some regard, even if it is (at the very minimum) not having application code always connecting to the database as a superuser. The proper way to use the database's management of security depends on your needs: if the DBMS supports row-level permissions and it's good enough, use it, otherwise do something else. In the case of web-apps with millions of users, then row-level permissions aren't a good fit (as you said). For other people not doing web apps, it might be.
This is not mutually exclusive from parameterizing and sanitizing input. You do both.
In the case of the IIS/SQL-Server attack, it seems that the user had the ability to read the catalogs to search for table names, and then had the direct rights to modify data. Why? With a high-profile database (e.g. one attached to an app that is public-facing to the internet), why are the tables wide-open?
1. Have your essential schema that holds the tables and important private internals, and revoke privileges on it. Follow the Principle Of Least Privilege. Here, no one but a superuser (which should only be the site's developers) has privileges.
2. Have one or more separate schemas where database users only get permissions to the essential schema via procedures and views in these schemas. If row-level security is about masking data, that is what you have these separate schemas for. Let the procedure and view strategy take care of the filtering. Procedures themselves are not arbitrarily structured from an API point of view: you pass in the parameters and await the result, the user has no direct control of what is performed.
3. Notice I'm not implying each application-level user *must* be a db-level user. At the minimum you just ensure that these users don't have direct access to the essential schema. If db-level users works, do that. It not, roll your own access-list strategy the procedures and views will use.
4. Sanitizing inputs and parameterized queries are not mutually exclusive of this strategy.
5. If ORM's are the main reason for connecting as a superuser and leaving tables wide-open, and if that is the main reason for relying on application code as the most important means of security, then that is a different problem. The problem is the ORM strategy, as the DBMS has the facilities needed to secure the data.