Joel Spolsky errors about SQL injection
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:
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.
Jake Munson
36 Yrs old
<cfquery name="users" datasource="#myDSN#">
select * from users;
select * from users
</cfquery>
This returns this error: "You have an error in your SQL syntax...near '; select * from users' at line..."
Just like with DB2, If you try to put a semicolon in with the intention of executing more than one SQL query from one cfquery tag, you'll get an error. Actually, in DB2 it's more secure than this, because the DB tries to execute the first statement and silently ignores the second one.
I /know/ the limitation is not in ColdFusion, because a lot of programmers like to do multiple queries in SQL Server, like to get the primary key that was just generated from an insert.
I'm curious how you were able to get MySQL 5 to run multiple queries and I was not. Are you just running a bunch of queries in your query tool? Because I know that works, that's a completely different subject. I'm talking about multiple queries from the outside. Also, it probably depends on the connection you're using. If you're connecting to MySQL ODBC, it probably allows it. I prefer to use the official JDBC driver from MySQL.
<cfquery name="test" datasource="#myDSN#">
select * from testjake;
delete from testjake
</cfquery>
Before running the above CF code, testJake had 199 rows. After, the table was empty.
This is exactly what Joel Spolsky was talking about in his blog post, but like I said, it doesn't work in the major non-Microsoft databases.