Let's say you are building a query from user input. Let's say (completely
ridiculous I know) that you are storing the login query. Please forgive the
sketchiness and bad form of this example. I don't actually handle login
this way, but I've seen many who do:
<Cfset qryString = "select * from users where useranme = '" & form.username
& "' and password = '" & form.password & "'">
Notice the inclusion of single quotes are necessary in the string because we
are dealing with character data.
If you tried the following:
<cfquery name="blah" datasource="#mydsn#">
#qryString#
</cfquery>
It would error out. That's because the CFQUERY tag automatically escapes
single quotes in variables. It would send:
select * from users where usnername = ''someUname'' and password =
''somepassword''
That's invalid syntax. Thus, you must use "preservesinglequotes( )" to
ensure valid syntax. Now, if a user in the password form field typed in:
'' OR username = 'Administrator
Viola'! He's in, because you didn't scrub the "password" variable. The
actual query ends up being:
select * from users where usnername = 'someUname' and password = '' OR
Username = 'Administrator'
If you had not used the "string building / preservesingleQuotes( )"
approach, then your friendly hacker would not have been able to circumvent
the character query. In other words, if your query was:
<cfquery name="blah" datasource="#mydsn#">
SELECT * FROM users WHERE username = '#form.username#'
AND Password = '#form.password#'
</cfquery>
.... his clever string would have produced this query:
select * from users where usnername = 'someUname' and password = ''' OR
Username = ''Administrator'''
In other words, looking for a password of 'OR Username = 'administrator'.
In fact, the use of a replacement string (like 3 ampersands) doesn't benefit
you from a security standpoint - although it may make your string easier to
store in the database. The problem is, the replace function that restores
the query may put it back into it's original malicious condition. It's
important to scrub the string of keywords and other potentially damaging
stuff BEFORE you replace the single quotes and store the data.
Another thing to note is that passing integers directly into a query is even
more dangerous. An injection attacker doesn't have to futz with single
quotes at all. He can even damage your data - without a lot of effort.
-Mark
-----Original Message-----
From: Matthew Small [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 10:22 AM
To: CF-Talk
Subject: RE: Get Actual Query Run
Can you explain that? I don't understand why preservesinglequotes is
necessary,m other than when writing the query to file, when sql
injection isn't a factor.
Matthew Small
IT Supervisor
Showstopper National Dance Competitions
3660 Old Kings Hwy
Murrells Inlet, SC 29576
843-357-1847
http://www.showstopperonline.com
-----Original Message-----
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 11:22 AM
To: CF-Talk
Subject: RE: Get Actual Query Run
Michael,
If you do this in production mode for some reason watch out. You will
be
forced to use "preservesinglequotes( )" to maintain your query. This
will
expose you to SQL injection unless you scrub all the user input first.
There
are some scrubbing UDFs on Ray's UDF site I believe.
-Mark
-----Original Message-----
From: Matthew Small [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 10:11 AM
To: CF-Talk
Subject: RE: Get Actual Query Run
So write the code that is in your cfquery into a file:
<cfquery datasource="dsn">
select * from table where id = #id#
</cfquery
<cfset querystring = " select * from table where id = #id#">
<cffile mode=write" variable="querystring">
I think you can get the idea from here
Matthew Small
IT Supervisor
Showstopper National Dance Competitions
3660 Old Kings Hwy
Murrells Inlet, SC 29576
843-357-1847
http://www.showstopperonline.com
-----Original Message-----
From: Michael Ross [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 11:02 AM
To: CF-Talk
Subject: Get Actual Query Run
I have a question. I have a query that is built depending on many
things, the where statement, order by, actual columns asked for that
kinda thing..... I want to save what the actual sql text is that ran
that query. Like what you can see in the debugging code. Does this
make sense? Any help would be great.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
