Jay, You are correct in implying that lazy or inexperienced programmers perpetuate this SQL exploit by allowing SQL statements to be high-jacked. However, the problem lies in poor programming techniques caused by building SQL statments through string concatentations. Your recommended solution reinforces these poor programming techniques. There are well-known best practices for modern database programming that will completely prevent these types of SQL exploits. One of the best practices is to never write code that builds SQL statements through string concatentations. Instead use stored procedures and/or parameterized queries. Most modern DBMSs such as Oracle, Sybase, and MS SQL Server support this. It is virtually impossible to hijack SQL when using stored procedures and/or parameterized queries. Another best practice is to never expose your database schema to external users. Trap database error messages so that the external user doesn't ever get a chance to see them. People trying to exploit your database will typically try to first create some sort of SQL syntax error so that they can glean information about your schema from the error message. It makes it extremely difficult to cause damage to a database if you don't know the names of the database objects (i.e., tables). Another best practice is to limit the security privileges of the user account accessing the database. A common programming technique with modern DBMSs such as Oracle, Sybase, and MS SQL Server is to wrap all database operations inside of stored procedures, remove all administrative privileges, remove all Select/Insert/Update/Delete privileges on all tables, and grant only execute privileges on the stored procedures to the user account accessing the database. Now, there is very little left for the hacker to exploit in the database. I know the original question was about mysql. I don't use mysql so I can't comment on it. Hopefully it supports some of the techniques that I talked about above.
This archive was generated by hypermail 2b30 : Mon Dec 31 2001 - 12:15:08 PST