Re: malformed sql queries

["Kevin Hegg" <khegg () sprockets com>]

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.