Today Joel Spolsky mentioned SQL injection on his blog, and stated that if you are in the practice of directly dumping strings from user input into your DB, it would be easy to hack your DB if you close the string, insert a semi-colon and create a second SQL statement like so:foo'; delete * from accountsJust enter the above ‘code’ into the form and you’ve manually deleted the contents of their accounts table, right (assuming they have a table named ‘accounts’)? Only if you’re using Microsoft SQL Server. SQL Server allows you to send multiple commands from an outside source. This is VERY insecure, for the above reason. In Joel’s example, the error message he got shows that the site he is referencing is powered by MySQL. I have dealt with Oracle and DB2 and I know those DBs don’t allow multiple sql statements in one ‘session’ from an external source (session means one cfquery tag in this case). I’m assuming that MySQL won’t allow this either (someone please correct me if I’m wrong). Update: I just tested this, thanks to a comment left by Jacek, and MySQL will not allow multiple SQL statements from one cfquery tag.We in the ColdFusion community know that you should always CFQueryParam your variables in queries. Not only is this safer (it would stop the above attack), but it usually makes your queries quicker. This is because ColdFusion will create a “Prepared Statement”, which is a fancy way of saying it will pre-compile the query. Then the DB just runs it without compilation. That said, Microsoft assumes that the developer is always going to use things like CFQueryParam in their code. Oracle and IBM, on the other hand, try not to let the developer make mistakes like this.
