preservesinglequotes.
PreserveSingleQuotes stops CF from escaping single quotes in the SQL.
This would mean that if you use it as below and somebody entered
"O'connor street" for Form.civicaddress, the query would throw an error.
There are a few cases where CF doesn't escape single quotes
automatically when using vars in the sql, and then you have to escape
them manually using Replace(var,"'","''","all"). Using <cfqueryparam>
fixes this too.
Pascal
> -----Original Message-----
> From: J E VanOver [mailto:[EMAIL PROTECTED]
> Sent: donderdag 5 februari 2004 22:52
> To: CF-Talk
> Subject: RE: Different query results in Access/ColdFusion
>
> For ANY character data, you must either use <CFQUERYPARAM> or
> preservesinglequotes() or you are BEGGING for trouble.
>
>
> SELECT * FROM tblPropertyInfo INNER JOIN tblRegistryInfo ON
> (tblPropertyInfo.PropID=tblRegistryInfo.PropID)
> AND (tblPropertyInfo.PropDate=tblRegistryInfo.PropDate)
> WHERE CivicAddressStreet LIKE
> '%#preservesinglequotes(Form.civicaddress)#%'
> AND Municipality = '#preservesinglequotes(Form.municipality)#'
> AND DocType = 'CONVEYANCE'
> ORDER BY CivicAddressNumber asc
>
> Or better:
>
> SELECT * FROM tblPropertyInfo INNER JOIN tblRegistryInfo ON
> (tblPropertyInfo.PropID=tblRegistryInfo.PropID)
> AND (tblPropertyInfo.PropDate=tblRegistryInfo.PropDate)
> WHERE CivicAddressStreet LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> value="%#Form.civicaddress#%">
> AND Municipality = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> value="#Form.municipality#">
> AND DocType = 'CONVEYANCE'
> ORDER BY CivicAddressNumber asc
>
> This form ALSO protects you from SQL invasion attacks --
> people entering SQL commands into form fields in order to
> cause mischief in your databases.
>
> Jevo
>
> -----Original Message-----
> From: Christina Robson [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 05, 2004 10:50 AM
> To: CF-Talk
> Subject: Re:Different query results in Access/ColdFusion
>
>
> Good idea, Bryan. :) Ok, here's my SQL statement! This
> gives me different results depending on what I sort on. I've
> yet to find a way to ensure it always gives me the right
> results, regardless of how I sort it. Argh. :)
>
> Thanks!
> Christina
>
> <cfquery name="getpropinfo" datasource="#application.dns_name2#"
> dbtype="ODBC">
>
> SELECT * FROM tblPropertyInfo INNER JOIN tblRegistryInfo ON
> (tblPropertyInfo.PropID=tblRegistryInfo.PropID)
> AND (tblPropertyInfo.PropDate=tblRegistryInfo.PropDate)
> WHERE CivicAddressStreet LIKE '%#Form.civicaddress#%'
> AND Municipality = '#Form.municipality#'
> AND DocType = 'CONVEYANCE'
> ORDER BY CivicAddressNumber asc
>
> </cfquery>
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
