Since there appears to be some debate on what cfqueryparam exactly does,
how it does that and what that means I prepared some example. Let's
consider the following:
CREATE TABLE tbl_test (testID INT, testText VARCHAR(100));
string = "normal string"
INSERT INTO tbl_test (testID, testText) VALUES (6, '#string#');
string = "scary string'); DROP TABLE tbl_test; --"
INSERT INTO tbl_test (testID, testText) VALUES (6, '#string#');
The first SQL statement is the table definition, so we all know what we
are talking about. The second is the SQL statement we would like to
perform. The third is the insert statement we would like to protect from
(the 2 dashes at the end indicate comment in SQL).
First step is to look how the normal insert would work in normal Java,
without using a prepared statement. Bare bones code looks like this:
sql = db.createStatement();
String sqlText = "INSERT INTO tbl_test (testID, testText) VALUES (6,
'normal string')";
sql.executeUpdate(sqlText);
So as we all can see, if you replace the insert statement with something
scary it might go wrong:
sql = db.createStatement();
String sqlText = "INSERT INTO tbl_test (testID, testText) VALUES (6,
'scary string'); DROP TABLE tbl_test; --')";
sql.executeUpdate(sqlText);
I presume it is also clear now why the WHERE clause is not the only that
needs to be protected.
Now to the bare bones code of the prepared statement in Java:
sqlText = "INSERT INTO tbl_test (testID, testText) VALUES (?,?)";
PreparedStatement ps = db.prepareStatement(sqlText);
ps.setInt(1,6);
ps.setString(2,"normal string");
ps.executeUpdate();
Yes, question marks. The first question mark is set to the INTEGER 6
(using the setInt() function), the second question mark is set to the
STRING "normal string" (using the setString() function).
So for the scary string it would look like:
sqlText = "INSERT INTO tbl_test (testID, testText) VALUES (?,?)";
PreparedStatement ps = db.prepareStatement(sqlText);
ps.setInt(1,6);
ps.setString(2,"scary string'); DROP TABLE tbl_test; --");
ps.executeUpdate();
Then, upon execution, the JDBC driver sends everything to the database
(I think the variables are actually send before the query statement but
that is not really important, the important part is that they are sent
separately). And since they arrive in the database as separate
variables, and not as some query statement that needs to be parsed,
there is no risk for it being treated the wrong way in the parser.
In fact, because of the explicit use of setInt() and setString(), even
the validation of datatypes is already done on the side of the client.
cfqueryparam implements this preparedStatement instead of the normal
createStatement. I hope this makes clear why SQL injection is quite
impossible if the implementation in the database and JDBC driver is correct.
The execution times of prepared statements.
Generally, a query that is being sent to a database as a pass-through
query goes through 3 phases: parsing, planning and execution. Parsing
means that the plain text that is being sent is translated to something
the database understands. Usually involves validation and matching
against schema objects (the "what does the query want" part). Next the
database determines how to do it. Which indexes to use, in which order
to sort/join etc. (the "how to do it" part). Finally, the query is
executed and the result is being sent back to the client (the "do it" part).
The less time the last part takjes, the more significant the first 2
parts are. Prepared statements to the rescue.
If a prepared statement is received by the server, it first looks
whether that statement is executed before. Easy way is to take the hash
from the statement and look in the cache if the hash is there (which
explains why case, spaces and the like do matter). If found, go straight
to step 3, the "do it" part, fill out the values in the query execution
plan and execute the query. If not found, execute step 1 and 2, and
cache the resulting query execution plan.
Most serious and some none serious databases support prepared
statements, which does not necessarily mean they cache execution plans,
just that they support the syntax.
HTH, additions welcome,
Jochem
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
