I understand, it's just that (I thought) you made it sound like SQL-injection-prevention techniques were more necessary when writing the output to file.
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 12:31 PM To: CF-Talk Subject: RE: Get Actual Query Run 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 Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
