ColdFusion and SQL

Question

ColdFusion and SQL

M Chapman

SSC Veteran

Points: 242
More actions
August 10, 2004 at 1:20 pm

#83530

Yes, this is probably a question for a ColdFusion thread, but here goes anyway:

When doing this:
<cfset state = "PA">
<cfset sqlstring = "select * from states where state = '#state#' ">
<cfquery name = "getstate" datasource = "ds">
#sqlstring#
</cfquery
produces:
select * from states where state = ''PA''
(by the way, ''PA'' is a double single quoted string in the query: Big NO-NO)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

greaseham Grasshopper Points: 14 More actions · Answer 1

Yep... that's a wierd one. Just ttried it and I get the same. I've been writing in ColdFusion for nearly 5 years now and never noticed that before. Probably because...

Creating the SQL string in a local variable is a very ASP thing to do. Using ColdFusion you can simply write:

SELECT * FROM states WHERE state = '#state#'

</cfquery>

However, if "state" is being submitted via a URL parameter then this exposes a possible security hole. If your database isn't locked down as tight as you can get it then people who know what they are doing can delete the content from a table or even drop a table completely. You can combat this using:

SELECT * FROM states WHERE state = <cfqueryparam value="URL.state" cfsqltype="cf_sql_varchar" maxLength="2">

</cfquery>

Using CFQUERYPARAM allows you to specify exactly what the database can receive, in this case a varchar/nvarchar parameter that is a maximum of 2 charachters long. Just change "maxLength" to the maximum possible length of the "state" field in your database.

You can extend this further buy using <CFTRY> & <CFCATCH> around your query block to redirect a user to an error page if the parameter supplied in the URL string is outside the specification in the <CFQUERYPARAM> tag... but that's a bit more involved and after all, this is a SQL forum. ;¬)

Hope this answers your question.

Greaseham

lxz20 Mr or Mrs. 500 Points: 592 More actions · Answer 2

You're probably working with MX which automatically escapes single quotes within <CFQUERY> tag (by adding another single quote).

Larry

M Chapman SSC Veteran Points: 242 More actions · Answer 3

Greasham...good call on the ASP thing; that's what I usually write in. I did figure out what to do in that case (and yes, lxz20, it is MX). There is an MX function called PreserveSingleQuotes() which i used to fix this. Thanks for your help guys.