RE: Question about cfqueryparam
You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery -Original Message- From: Joshua OConnor-Rose [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:17 To: CF-Talk Subject: Question about cfqueryparam Sorry if this question has been answered already. I searched the list but putting that particular tagname returns plenty and most of it is advice to use it. For search pages I started to put the where statement together into a variable request.filter this worked fine but we just upgraded the server from 4.5 to 6.1 and I wanted to start throwing in cfqueryparam. So when I use this: cfset request.filter = where 0=0 cfif len(attributes.office_num) cfset request.filter = request.filter and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif And then do this: cfquery datasource=media name=get_office select office_id from office #request.filter# /cfquery I get an error. I can't quite figure out what I'm missing any help? -Joshua O'Connor-Rose -All is Good __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Question about cfqueryparam
Hi Josh, cfqueryparam is meant for use inside of your cfquery tags becuase it does more than string parsing. Try this out: cfquery datasource=media name=get_office select office_id from office where office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfquery Cheers, Joe - Original Message - From: Joshua OConnor-Rose [EMAIL PROTECTED] Date: Thu, 29 Jul 2004 08:16:59 -0700 (PDT) Subject: Question about cfqueryparam To: CF-Talk [EMAIL PROTECTED] Sorry if this question has been answered already. I searched the list but putting that particular tagname returns plenty and most of it is advice to use it. For search pages I started to put the where statement together into a variable request.filter this worked fine but we just upgraded the server from 4.5 to 6.1 and I wanted to start throwing in cfqueryparam. So when I use this: cfset request.filter = where 0=0 cfif len(attributes.office_num) cfset request.filter = request.filter and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif And then do this: cfquery datasource=media name=get_office select office_id from office #request.filter# /cfquery I get an error. I can't quite figure out what I'm missing any help? -Joshua O'Connor-Rose -All is Good __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Question about cfqueryparam
thanks. gee I thought I had something good going on. ah well. -Joshua O'Connor-Rose -All is Good --- Pascal Peters [EMAIL PROTECTED] wrote: You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery -Original Message- From: Joshua OConnor-Rose [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:17 To: CF-Talk Subject: Question about cfqueryparam Sorry if this question has been answered already. I searched the list but putting that particular tagname returns plenty and most of it is advice to use it. For search pages I started to put the where statement together into a variable request.filter this worked fine but we just upgraded the server from 4.5 to 6.1 and I wanted to start throwing in cfqueryparam. So when I use this: cfset request.filter = where 0=0 cfif len(attributes.office_num) cfset request.filter = request.filter and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif And then do this: cfquery datasource=media name=get_office select office_id from office #request.filter# /cfquery I get an error. I can't quite figure out what I'm missing any help? -Joshua O'Connor-Rose -All is Good __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Question about cfqueryparam
I forgot to delete #request.filter# in the query below -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:23 To: CF-Talk Subject: RE: Question about cfqueryparam You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Question about cfqueryparam
I'm back to this now Anybody seen this before? [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ 'active = 1 and adcompany = 4 where office_num in (Pa_RaM000,Pa_RaM001,Pa_RaM002)'. it says my sql looks different too: select office.office_id,office.office_num,office.office_name,office.city,state.state from office inner join state on office.state=state.state_id where active = 1 and adcompany = 4 where office_num in ( (param 1) , (param 2) , (param 3) ) order by office.office_num -Joshua O'Connor-Rose -All is Good --- Pascal Peters [EMAIL PROTECTED] wrote: I forgot to delete #request.filter# in the query below -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:23 To: CF-Talk Subject: RE: Question about cfqueryparam You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Question about cfqueryparam
nevermind sorry to be so hasty -joshua o'connor-rose -all is good --- Joshua OConnor-Rose [EMAIL PROTECTED] wrote: I'm back to this now Anybody seen this before? [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ 'active = 1 and adcompany = 4 where office_num in (Pa_RaM000,Pa_RaM001,Pa_RaM002)'. it says my sql looks different too: select office.office_id,office.office_num,office.office_name,office.city,state.state from office inner join state on office.state=state.state_id where active = 1 and adcompany = 4 where office_num in ( (param 1) , (param 2) , (param 3) ) order by office.office_num -Joshua O'Connor-Rose -All is Good --- Pascal Peters [EMAIL PROTECTED] wrote: I forgot to delete #request.filter# in the query below -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:23 To: CF-Talk Subject: RE: Question about cfqueryparam You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Question about cfqueryparam
Hey Josh, You're missing your AND operator and doubling up on the WHERE.Try this: from office inner join state on office.state=state.state_id where active = 1 and adcompany = 4 AND office_num in (cfquery... -joe - Original Message - From: Joshua OConnor-Rose [EMAIL PROTECTED] Date: Thu, 29 Jul 2004 10:41:57 -0700 (PDT) Subject: RE: Question about cfqueryparam To: CF-Talk [EMAIL PROTECTED] I'm back to this now Anybody seen this before? [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query _expression_ 'active = 1 and adcompany = 4 where office_num in (Pa_RaM000,Pa_RaM001,Pa_RaM002)'. it says my sql looks different too: select office.office_id,office.office_num,office.office_name,office.city,state.state from office inner join state on office.state=state.state_id where active = 1 and adcompany = 4 where office_num in ( (param 1) , (param 2) , (param 3) ) order by office.office_num -Joshua O'Connor-Rose -All is Good --- Pascal Peters [EMAIL PROTECTED] wrote: I forgot to delete #request.filter# in the query below -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: 29 July 2004 17:23 To: CF-Talk Subject: RE: Question about cfqueryparam You can't use cfqueryparam like this, it won't get evaluated. It has to be in the cfquery. cfquery datasource=media name=get_office select office_id from office #request.filter# where 0=0 cfif len(attributes.office_num) and office_num in (cfqueryparam list='Yes' value='#attributes.office_num#' cfsqltype='CF_SQL_VARCHAR') /cfif /cfquery [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Question about CFQUERYPARAM and GUIDs
At 11:16 AM -0600 2/23/04, Justin Hansen wrote: I use CF_SQL_VARCHAR for GUIDs. It works just fine. What if a string of characters, formatted just the same as a GUID but with one less character, was used?According to my coworker, this would validate just the same as a real GUID. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ I'm in Bowie, MD, USA, halfway between DC and Annapolis. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
Any character string will validate if you use CF_SQL_VARCHAR, regardless of
format.If you want to validate a GUID, you'll have to use something more
than just CFQUERYPARAM.Easiest is probably a simple Refind call, with the
appropriate RE.I don't know the exact format of a GUID, but it should be
something like this (change the numbers, and maybe add another segment):
isValid = refind(^[0-9a-fA-F]{5}-[0-9a-fA-F]{5}-[0-9a-fA-F]{5}$, myGuid);
Cheers,
barneyb
-Original Message-
From: Lola Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 24, 2004 8:32 AM
To: CF-Talk
Subject: RE: Question about CFQUERYPARAM and GUIDs
At 11:16 AM -0600 2/23/04, Justin Hansen wrote:
I use CF_SQL_VARCHAR for GUIDs. It works just fine.
What if a string of characters, formatted just the same as a GUID but
with one less character, was used?According to my coworker, this
would validate just the same as a real GUID.
--
Lola - mailto:[EMAIL PROTECTED]
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Question about CFQUERYPARAM and GUIDs
Lola Lee wrote: At 11:16 AM -0600 2/23/04, Justin Hansen wrote: I use CF_SQL_VARCHAR for GUIDs. It works just fine. What if a string of characters, formatted just the same as a GUID but with one less character, was used?According to my coworker, this would validate just the same as a real GUID. It would. But that is the risk you take when you use datatypes that are neither standardized in SQL nor in JDBC. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
At 8:38 AM -0800 2/24/04, Barney Boisvert wrote:
Any character string will validate if you use CF_SQL_VARCHAR, regardless of
format.If you want to validate a GUID, you'll have to use something more
than just CFQUERYPARAM.Easiest is probably a simple Refind call, with the
appropriate RE.I don't know the exact format of a GUID, but it should be
something like this (change the numbers, and maybe add another segment):
isValid = refind(^[0-9a-fA-F]{5}-[0-9a-fA-F]{5}-[0-9a-fA-F]{5}$, myGuid);
Isn't this counterintuitive?
We're being told to use CFQUERYPARAM in cfqueries.But if I
understand it correctly, there is no CF_SQL types that handle GUIDs.
What we need to be able to do is something like this:
cfquery verifyUser
select * from user
where UserID = cfqueryparam type=CF_SQL_xxx value=GUID
/cfquery
--
Lola - mailto:[EMAIL PROTECTED]
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Question about CFQUERYPARAM and GUIDs
We're being told to use CFQUERYPARAM in cfqueries. But if I understand it correctly, there is no CF_SQL types that handle GUIDs. What we need to be able to do is something like this: cfquery verifyUser select * from user where UserID = cfqueryparam type=CF_SQL_xxx value=GUID /cfquery I don't think that UUID/GUID is a defined data type within SQL; it's specific to SQL Server. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
CFQUERYPARAM protects your database.It includes very primitive data
validation for standardized SQL types.That validation is for protection
only, in most cases, it's completely insufficient for the specific data of
an app.For example, the INTEGER type will happily let you store a persons
age that is negative or in the millions, as long as it's an integer, so you
almost always have to provide additional validation.
This is even worse with non-standard data types, such as SQL Server's GUID
type, MySQL's SET and ENUM types, etc.The best you can do is to use a
CF_SQL_VARCHAR, which will protect the database as well, but won't provide
you with any validation, so you have to provide external validation.
Bottom line, CFQUERYPARAM is an extra layer of security on your database,
it's not for validation.It can be used that way, but only in a supporting
role.
Cheers,
barneyb
-Original Message-
From: Lola Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 24, 2004 8:54 AM
To: CF-Talk
Subject: RE: Question about CFQUERYPARAM and GUIDs
At 8:38 AM -0800 2/24/04, Barney Boisvert wrote:
Any character string will validate if you use
CF_SQL_VARCHAR, regardless of
format.If you want to validate a GUID, you'll have to use
something more
than just CFQUERYPARAM.Easiest is probably a simple Refind
call, with the
appropriate RE.I don't know the exact format of a GUID,
but it should be
something like this (change the numbers, and maybe add
another segment):
isValid =
refind(^[0-9a-fA-F]{5}-[0-9a-fA-F]{5}-[0-9a-fA-F]{5}$, myGuid);
Isn't this counterintuitive?
We're being told to use CFQUERYPARAM in cfqueries.But if I
understand it correctly, there is no CF_SQL types that handle GUIDs.
What we need to be able to do is something like this:
cfquery verifyUser
select * from user
where UserID = cfqueryparam type=CF_SQL_xxx value=GUID
/cfquery
--
Lola - mailto:[EMAIL PROTECTED]
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Question about CFQUERYPARAM and GUIDs
Isn't this counterintuitive?
I'd answer no.What you're asking for is akin to a cfsqltype to
enforce, say, a telephone number to 999-999-.That's not what
cfqueryparam is about.You're really talking about a formatting mask
and not validating a standard SQL datatype.
Matt Robertson [EMAIL PROTECTED]
MSB Designs, Inc.http://mysecretbase.com
-Original Message-
From: Lola Lee [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 24, 2004 8:54 AM
To: CF-Talk
Subject: RE: Question about CFQUERYPARAM and GUIDs
At 8:38 AM -0800 2/24/04, Barney Boisvert wrote:
Any character string will validate if you use CF_SQL_VARCHAR,
regardless of
format.If you want to validate a GUID, you'll have to use something
more
than just CFQUERYPARAM.Easiest is probably a simple Refind call, with
the
appropriate RE.I don't know the exact format of a GUID, but it should
be
something like this (change the numbers, and maybe add another
segment):
isValid = refind(^[0-9a-fA-F]{5}-[0-9a-fA-F]{5}-[0-9a-fA-F]{5}$,
myGuid);
Isn't this counterintuitive?
We're being told to use CFQUERYPARAM in cfqueries.But if I
understand it correctly, there is no CF_SQL types that handle GUIDs.
What we need to be able to do is something like this:
cfquery verifyUser
select * from user
where UserID = cfqueryparam type=CF_SQL_xxx value=GUID
/cfquery
--
Lola - mailto:[EMAIL PROTECTED]
http://www.lolajl.net | Blog at http://www.lolajl.net/blog/
I'm in Bowie, MD, USA, halfway between DC and Annapolis.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Question about CFQUERYPARAM and GUIDs
Lola Lee wrote:
At 8:38 AM -0800 2/24/04, Barney Boisvert wrote:
Any character string will validate if you use CF_SQL_VARCHAR, regardless of
format.If you want to validate a GUID, you'll have to use something more
than just CFQUERYPARAM.Easiest is probably a simple Refind call, with the
appropriate RE.I don't know the exact format of a GUID, but it should be
something like this (change the numbers, and maybe add another segment):
isValid = refind(^[0-9a-fA-F]{5}-[0-9a-fA-F]{5}-[0-9a-fA-F]{5}$, myGuid);
Isn't this counterintuitive?
No. SQL allows you to even create your own datatypes. Why would
some cf_sql-type automagically exists when I write my own
datatype for GUIDs or ISBN numbers?
The SQL standard has a number of predefined datatypes. For those
cf_sql-types are provided. If you wish to use other datatypes you
are on your own.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Question about CFQUERYPARAM and GUIDs
From: Lola Lee Isn't this counterintuitive? We're being told to use CFQUERYPARAM in cfqueries.But if I understand it correctly, there is no CF_SQL types that handle GUIDs. What we need to be able to do is something like this: cfquery verifyUser select * from user where UserID = cfqueryparam type=CF_SQL_xxx value=GUID /cfquery Not at all What you have to remember is that a GUID is just a string of characters - different systems produce them differently CF, SQL Server and other apps won't produce the same data layout If you expand it for GUID, people will ask for a data-type for emails, Zip, telephone numbers and credit cards (But they're valid data-types, right?) You're meant to do the validation yourself, and then pass your data to the server [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Question about CFQUERYPARAM and GUIDs
Philip Arnold wrote: What you have to remember is that a GUID is just a string of characters - different systems produce them differently Actually, a GUID is a 128 bit integer that is produced according to the same algorithm everywhere. CF, SQL Server and other apps won't produce the same data layout Even the string representation of a GUID is the same everywhere. There is however a difference between the string representation of a GUID and a UUID. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
Even the string representation of a GUID is the same everywhere. There is however a difference between the string representation of a GUID and a UUID. Sorry, I meant UUID/GUID g [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
Lola Lee wrote What if a string of characters, formatted just the same as a GUID but with one less character, was used?According to my coworker, this would validate just the same as a real GUID. It sure would.Read the earlier posts on this.You are mistaking cfqueryparam for a data input validator, and that's not its purpose. One tool that is can be found here: http://cflib.org/udf.cfm?ID=742 And besides, when would your example ever happen?When would you expose a GUID to user input in the first place?Shouldn't you be generating these yourself, *way* outside the control of mere mortals? http://cflib.org/udf.cfm?ID=54 Matt Robertson [EMAIL PROTECTED] MSB Designs, Inc.http://mysecretbase.com [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Question about CFQUERYPARAM and GUIDs
I use CF_SQL_VARCHAR for GUIDs. It works just fine. Justin Hansen Project Manager Uhlig Communications _ From: Lola Lee [mailto:[EMAIL PROTECTED] Sent: Monday, February 23, 2004 11:06 AM To: CF-Talk Subject: Question about CFQUERYPARAM and GUIDs At work we use SQL Server, and for the project we're using GUIDs for the id columns in several tables.Is there a CFSQLTYPE available for use with CFQUERYPARAM?Looking at the online documentation does not seem to indicate as cuh.What about CF_SQL_IDSTAMP? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Check out this blog:http://www.denbeste.nu I'm in Bowie, MD, USA, halfway between DC and Annapolis. _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
