S. Isaac Dealey wrote:
>>
>><cfset string="~'; DROP TABLE tablename --">
>><cfquery ....>
>> SELECT * FROM tablename WHERE field = '#string#'
>></cfquery>
>>
>>It will result in the table being dropped, and CF will not
>>filter out
>>the DROP statement, even if you restricted its use (at
>>least in MX).
>>
>>Use cfqueryparam.
>
>
> Given a db that uses ~ to bound string values instead of single or double
> quotes, this is ture, but then you would not be escaping the single or
> double-quotes, you would be escaping the ~ symbol. Generally speaking I
> would imagine that if they were using a db which used an unusual character
> to delimit strings like ~ they would know that, and they would be asking
> about that, rather than asking about single or double quotes ...
True. But remember code portability. And who wants to know (does know)
all the stuff from the different databases by hard. Sure, you can Google
for it and it is pretty easy to find for 3 or 4 or even 10 databases.
But how many databases are there? And do you really want to code
differently for each of them?
> But this does raise the question: does the queryparam tag know what the db
> uses as a string delimiter (as a result of drawing information from the db
> drivers i would imagine) or does it assume single quotes? If cfqueryparam
> assumes single quotes, then it won't help any more than trying to manually
> escape single or double quotes...
cfqueryparam doesn't need to know.
Variables passed using cfqueryparam are bound to a certain type. In MX
with JDBC, that happens by calling the class PreparedStatement and
setting variables with a type (i.e. setString, setInt, setXXX). Then,
the variables are passed using the appropriate java way, whatever that
may be. Next, the JDBC driver takes care of doubling/escaping whatever
is necessary. And typically the JDBC driver is aware of the specific
requirements of the database (that is why you need a different one for
each database).
Normal cfqueries get their quotes doubled by CF (else a function like
PreserveSingleQuotes() would not be very usefull). And it is my
understanding that that is all CF does, double the single quotes of
strings (else there would be PreserveSingleSomething() functions as well).
> I was under the impression also that single quotes were the standard for SQL
> string delimiters? Is SQL a standard? ... In which case it would make sense
> for the CF server to simply use what the standard specifies and not worry
> about non-standard databases or database servers.
Single quotes are the standard SQL delimiters for stirngs.
I do not know what is standard for escape characters and what else
possibly might exist. But the last time I checked the SQL spec was about
1600 pages and I find it much easier to use cfqueryparam as to read all
of those and then think of a solution myself.
And then there is always the question how strictly databases adhere to
standards. Let's take the double quote for example. IIRC, according to
the SQL standard, double quotes are identifier quotes, that make
anything in between them identifiers (names of column, tables, indexes
etc.). So SELECT "Select" FROM "From" WHERE "Where" = "WHere" is
perfectly legal SQL to select the column "Select" from the table "From"
where the column "Where" is equal to the column "WHere" (notice the H
instead of the h, those are two different columns).
With how many databases does this work? (script below)
I have more faith in a JDBC driver knowing what the database it was
designed for needs and CF being JDBC compliant, as in a database (any
database) being completely SQL standard compliant.
Jochem
CREATE TABLE "From" (
"Select" INTEGER,
"Where" INTEGER,
"WHere" INTEGER
);
INSERT INTO "From" (
"Select",
"Where",
"WHere"
)
VALUES (
7,
1,
1
);
INSERT INTO "From" (
"Select",
"Where",
"WHere"
)
VALUES (
1,
2,
3
);
-- This final select should return the number 7
SELECT
"Select"
FROM
"From"
WHERE
"Where" = "WHere";
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
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
