RE: SQL query style (WAS: SQL search query)
James, Not sure about the join fashion trend (I use the WHERE clause), but I'm pretty sure using cfqueryparam increases query speeds and improves security (from unautorised users: cfqueryparam Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times. This tag is nested within a cfquery tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation. Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. >From http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: 16 June 2004 11:47 am To: CF-Talk Subject: SQL query style (WAS: SQL search query) While we are on the subject I have noticed recently that more and ore people are joining tables using the... FROM table1 x JOIN table2 y ON x.ID = y.ID And I am wondering if there is a reason for this.Sure I use this syntax for my outer joins but inner joins I still do the old fashioned way in the where clause, for example FROMtable1 x, table2 y WHERE x.ID = y.ID This has the added benefit of taking the place of the 'WHERE 0=0' line we recently discussed.What are the benefits of one form of inner join over the other or is it just today's SQL fashion trend? Secondly I notice more and more use of the cfqueryparam tag, what is wrong with simply using WHERE x.ID= #form.id# AND y.var = '#url.string#' type formatting?I have been doing this for about 5 years now and have NEVER found a need for the cfqueryparam tag, I just find it makes the code harder to read. Comments anyone? -- Jay [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
James Smith wrote: FROM table1 x JOIN table2 y ON x.ID = y.ID FROMtable1 x, table2 y WHERE x.ID = y.ID This has the added benefit of taking the place of the 'WHERE 0=0' line we recently discussed.What are the benefits of one form of inner join over the other or is it just today's SQL fashion trend? IMHO the easiest way to write anything but the simplest queries is: - write the FROM to include all data and relations - add the WHERE to filter records - add the SELECT to indicate the exact fields you want - add, sorting and grouping as needed Using the JOIN keyword in the FROM helps by keeping the logic to relate tables to eachother separate from the logic to filter the desired rows. But in the end it is a matter of style and preference. Secondly I notice more and more use of the cfqueryparam tag, what is wrong with simply using WHERE x.ID= #form.id# AND y.var = '#url.string#' type formatting? STA. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
Rob, It's more a time / aesthetics way of doing things.The SQL 1 standard didn't support join, inner join, or outer join in the way we use them now, and each vendor started to cook up their own way of doing it.When the ANSI 92 syntax was brought about, they needed to come up with a way to standardize joining tables that didn't break the proprietary methods vendors had invented, so they moved it up into the FROM clause via the JOIN keywords we use now. In theory, using the ANSI 92 syntax (not using the WHERE clause) will create SQL that could port better to other RDBMS. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 6:53 AM To: CF-Talk Subject: RE: SQL query style (WAS: SQL search query) James, Not sure about the join fashion trend (I use the WHERE clause), but I'm pretty sure using cfqueryparam increases query speeds and improves security (from unautorised users: cfqueryparam Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times. This tag is nested within a cfquery tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation. Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. From http://livedocs.macromedia.com/coldfusion/6.1/htmldocs/tags-b20.htm -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: 16 June 2004 11:47 am To: CF-Talk Subject: SQL query style (WAS: SQL search query) While we are on the subject I have noticed recently that more and ore people are joining tables using the... FROM table1 x JOIN table2 y ON x.ID = y.ID And I am wondering if there is a reason for this.Sure I use this syntax for my outer joins but inner joins I still do the old fashioned way in the where clause, for example FROMtable1 x, table2 y WHERE x.ID = y.ID This has the added benefit of taking the place of the 'WHERE 0=0' line we recently discussed.What are the benefits of one form of inner join over the other or is it just today's SQL fashion trend? Secondly I notice more and more use of the cfqueryparam tag, what is wrong with simply using WHERE x.ID= #form.id# AND y.var = '#url.string#' type formatting?I have been doing this for about 5 years now and have NEVER found a need for the cfqueryparam tag, I just find it makes the code harder to read. Comments anyone? -- Jay _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
OK, if it improves performance it is worth using, but I am not sure what the requirement for verifying the data type is, almost all queries that use a variable are using one that you as the programmer have passed to it and therefore have control of its verification before it ever gets to the query, even if it is posted from a form we have the data validation of the CFFORM tag and the CFINPUT types. I also don't understand the 'unauthorized users' bit.Surely any query you write and execute in a cfm page would have the coldfusion server as the user, therefore if the query is being run your unauthorised user has already bypassed your coldfusion security and coldfusion will be authorised as far as the DB is concerned. I realise I must be missing the point here but would appreciate it if someone could clarify for me. -- Jay Verifies the data type of a query parameter and, for DBMSs that support bind variables, enables ColdFusion to use bind variables in the SQL statement. Bind variable usage enhances performance when executing a cfquery statement multiple times. This tag is nested within a cfquery tag, embedded in a query SQL statement. If you specify optional parameters, this tag performs data validation. Macromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
James Smith wrote: OK, if it improves performance it is worth using, but I am not sure what the requirement for verifying the data type is, almost all queries that use a variable are using one that you as the programmer have passed to it and therefore have control of its verification before it ever gets to the query, even if it is posted from a form we have the data validation of the CFFORM tag and the CFINPUT types. And you think the average cfform and cfinput tag is sufficient? I don't. I also don't understand the 'unauthorized users' bit. Authorization is not relevant. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
OK, if it improves performance it is worth using, but I am not sure what the requirement for verifying the data type is, almost all queries that use a variable are using one that you as the programmer have passed to it and therefore have control of its verification before it ever gets to the query, even if it is posted from a form we have the data validation of the CFFORM tag and the CFINPUT types. And you think the average cfform and cfinput tag is sufficient? I don't. OK, but even if the wrong data type is supplied to the query what will happen, you will get an error.If the cfqueryparam detects an incorrect data type it will throw an error yes?So what is the difference?Would you care to explain your answer rather than just taking pot shots at me? I also don't understand the 'unauthorized users' bit. Authorization is not relevant. >From Docs. QUOTEMacromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users./QUOTE Macromedia clearly think it is relevant, would you care to elaborate on why you think it isn't? -- Jay [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
James, CFQUERYPARAM does provide a layer of typing that running a basic query misses.For instance, if you have this query: SELECT firstname, lastname FROM employees WHERE employeeId = #url.employeeId# and I change the value of employeeId=2 in my address bar to something like: 2;SELECT password AS firstname, lastname FROM employees WHERE employeeId = 2 ...I can probably manipulate your database to do what I please.It's called a SQL injection attack, and CFQUERYPARAM is one very good way to prevent it, as it creates prepared statements that take parameters, not dynamic formed SQL.For more info, check out this article: http://www.macromedia.com/devnet/mx/coldfusion/articles/cfqueryparam.htm l -Joe -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 8:01 AM To: CF-Talk Subject: RE: SQL query style (WAS: SQL search query) OK, if it improves performance it is worth using, but I am not sure what the requirement for verifying the data type is, almost all queries that use a variable are using one that you as the programmer have passed to it and therefore have control of its verification before it ever gets to the query, even if it is posted from a form we have the data validation of the CFFORM tag and the CFINPUT types. And you think the average cfform and cfinput tag is sufficient? I don't. OK, but even if the wrong data type is supplied to the query what will happen, you will get an error.If the cfqueryparam detects an incorrect data type it will throw an error yes?So what is the difference?Would you care to explain your answer rather than just taking pot shots at me? I also don't understand the 'unauthorized users' bit. Authorization is not relevant. From Docs. QUOTEMacromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users./QUOTE Macromedia clearly think it is relevant, would you care to elaborate on why you think it isn't? -- Jay _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
James Smith wrote: And you think the average cfform and cfinput tag is sufficient? I don't. OK, but even if the wrong data type is supplied to the query what will happen, you will get an error. No. You will get a different SQL statement executed as you intended to be executed. If the cfqueryparam detects an incorrect data type it will throw an error yes?So what is the difference?Would you care to explain your answer rather than just taking pot shots at me? Google for SQL Injection Attack. I also don't understand the 'unauthorized users' bit. Authorization is not relevant. From Docs. QUOTEMacromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users./QUOTE Macromedia clearly think it is relevant, would you care to elaborate on why you think it isn't? Using an SQL Injection Attack doesn't change the user authentication or authorization, it changes the SQL statement. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
On Wed, 16 Jun 2004 08:06:16 -0400, Joe Rinehart wrote: CFQUERYPARAM does provide a layer of typing that running a basic query misses.For instance, if you have this query: SELECT firstname, lastname FROM employees WHERE employeeId = #url.employeeId# and I change the value of employeeId=2 in my address bar to something like: 2;SELECT password AS firstname, lastname FROM employees WHERE employeeId = 2 ...I can probably manipulate your database to do what I please.It's called a SQL injection attack, and CFQUERYPARAM is one very good way to prevent it, as it creates prepared statements that take parameters, not dynamic formed SQL.For more info, check out this article: http://www.macromedia.com/devnet/mx/coldfusion/articles/cfqueryparam.htm l Think of a more dangerous URL I'll change the URL variable to 2; drop table employees ' You can do the validation with Val and such, but you're still open to SQL Injection Attacks if you're not careful You should ALWAYS use CFQUERYPARM on EVERY query, no matter what [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
Thanks to Joe and Philip for helpful, explanatory answers.Jochem, you must have got out of bed on the wrong side today because you are normally very helpful on this list, today, not so much. I will be using CFQUERYPARAM from today. -- Jay [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
Was trying to be nice.We all know command line access is only one stored procedure away :). -joe -Original Message- From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 8:14 AM To: CF-Talk Subject: Re: SQL query style (WAS: SQL search query) On Wed, 16 Jun 2004 08:06:16 -0400, Joe Rinehart wrote: CFQUERYPARAM does provide a layer of typing that running a basic query misses.For instance, if you have this query: SELECT firstname, lastname FROM employees WHERE employeeId = #url.employeeId# and I change the value of employeeId=2 in my address bar to something like: 2;SELECT password AS firstname, lastname FROM employees WHERE employeeId = 2 ...I can probably manipulate your database to do what I please.It's called a SQL injection attack, and CFQUERYPARAM is one very good way to prevent it, as it creates prepared statements that take parameters, not dynamic formed SQL.For more info, check out this article: http://www.macromedia.com/devnet/mx/coldfusion/articles/cfqueryparam.htm l Think of a more dangerous URL I'll change the URL variable to 2; drop table employees ' You can do the validation with Val and such, but you're still open to SQL Injection Attacks if you're not careful You should ALWAYS use CFQUERYPARM on EVERY query, no matter what _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
I agree in theory, but you can't use it with cached queries. You should ALWAYS use CFQUERYPARM on EVERY query, no matter what [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
Hi Pascal, Yup, that's certainly a disadvantage, but here's the rules I follow for it: 1.I don't use CACHEDWITHIN or CACHEDAFTER on a query that relies on any input the ever came from a user.Use it for SELECT abbreviation FROM states, but not SELECT fullname FROM states WHERE fullname = '#form.fullname#'. 2.If I need to cache something that contains anything that was, at one time, user input, I'll use variable based cashing instead, by caching the query into a persistent scope.cfquery name=session.qStateFullName datasource=#dsn#SELECT fullname FROM states WHERE fullname = '#form.fullname#'/cfquery -Joe -Original Message- From: Pascal Peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 8:27 AM To: CF-Talk Subject: RE: SQL query style (WAS: SQL search query) I agree in theory, but you can't use it with cached queries. You should ALWAYS use CFQUERYPARM on EVERY query, no matter what _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
On Wed, 16 Jun 2004 14:27:24 +0200, Pascal Peters wrote: You should ALWAYS use CFQUERYPARM on EVERY query, no matter what I agree in theory, but you can't use it with cached queries. Store the queries in a persistant scope, such as Application - it's simple enough and gives you just as much control [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
Pascal Peters wrote: I agree in theory, but you can't use it with cached queries. So tell Macromedia to fix cfqueryparam: http://www.macromedia.com/go/wish/ Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: SQL query style (WAS: SQL search query)
Except that a persistent scope doesn't automatically handle the cachedafter/within type stuff, and if the recordset depends on url.id for example then you'll have to incorporate that fact into how/where you store the persistent variable. One way to use queryparam AND cachedwithin/after would be to use a Query of Query with catch/try: If the QofQ is cached then everything is fine. If there is no cache (ie either doesn't exist or doesn't match the cachedwithin/after attribute) then the error is cfcaught, dB is hit, and this recordset is cached in the QofQ. cftry cfquery name=myQ dbtype=query cachedwithin=...SELECT * FROM myQ WHERE '#hash(url.id)# = '#hash(url.id)#/cfquery cfcatch cfquery name=myQ datasource=#request.dsn# SELECT * FROM myTable WHERE ID = cfqueryparam value=#url.id# cfsqltype=CF_SQL_INTEGER /cfquery cfquery name=myQ dbtype=query cachedwithin=...SELECT * FROM myQ WHERE '#hash(url.id)#' = '#hash(url.id)#/cfquery /cfcatch /cftry I've sucessfully used this technique to cache results from verity, to allow paging through large recordsets without hitting verity again, and recordsets returned from CFDIRECTORY, and I don't see why it wouldn't work for normal SQL statements. In practice though, for the above example, I would just validate #url.id# up to the gills to make sure it was an integer, then run the query without cfqueryparam. Cheers Bert From: Philip Arnold [mailto:[EMAIL PROTECTED] Sent: 16 June 2004 13:47 To: CF-Talk Subject: Re: SQL query style (WAS: SQL search query) On Wed, 16 Jun 2004 14:27:24 +0200, Pascal Peters wrote: You should ALWAYS use CFQUERYPARM on EVERY query, no matter what I agree in theory, but you can't use it with cached queries. Store the queries in a persistant scope, such as Application - it's simple enough and gives you just as much control [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
QUOTEMacromedia recommends that you use the cfqueryparam tag within every cfquery tag, to help secure your databases from unauthorized users./QUOTE Macromedia clearly think it is relevant, would you care to elaborate on why you think it isn't? They're using the phrase unauthorized users pretty loosely within their documentation. What they really mean is that people who can legitimately run SQL queries that you've written, may also be able to rewrite those queries using SQL injection attacks. 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] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
While we are on the subject I have noticed recently that more and ore people are joining tables using the... FROM table1 x JOIN table2 y ON x.ID = y.ID And I am wondering if there is a reason for this.Sure I use this syntax for my outer joins but inner joins I still do the old fashioned way in the where clause, for example FROMtable1 x, table2 y WHERE x.ID = y.ID This has the added benefit of taking the place of the 'WHERE 0=0' line we recently discussed.What are the benefits of one form of inner join over the other or is it just today's SQL fashion trend? It's hardly a fashion trend - it's the law (ANSI SQL 92 specification). Most modern databases support explicit JOINs. It provides two benefits. Jochem mentioned that it separates filters from join conditions. As a byproduct of this, it prevents unintentional Cartesian products or cross joins, which can easily happen when you perform joins in a WHERE clause. 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] [Donations and Support]
RE: SQL query style (WAS: SQL search query)
Since I started this thread and am thankful for the help, my query works with just one flaw: it displays duplicate records. I've tried the SQL in two ways: SELECTDISTINCT p.ponumber, i.item, p.invoicenumber, p.vendor, p.cnumber, i.cnumber, p.state, p.podate FROM purchaseorders p JOIN itemsordered i ON p.cnumber=i.cnumber Where 0=0 and SELECT p.ponumber, i.item, p.invoicenumber, p.vendor, p.cnumber, i.cnumber, p.state, p.podate FROM purchaseorders p JOIN itemsordered i ON p.cnumber=i.cnumber Where Exists ( Select * from itemsordered i where p.cnumber=i.cnumber ) But is still grabs duplicate records from the itremsordered table. There the cnumber field is displayed for each item that is ordered. The purchaseorders table has the cnumber once since it just contains contact info. How can I just display one recordfor each cnumber when I do a search of all records for instance? Robert O. -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 12:59 PM To: CF-Talk Subject: RE: SQL query style (WAS: SQL search query) While we are on the subject I have noticed recently that more and ore people are joining tables using the... FROM table1 x JOIN table2 y ON x.ID = y.ID And I am wondering if there is a reason for this.Sure I use this syntax for my outer joins but inner joins I still do the old fashioned way in the where clause, for example FROMtable1 x, table2 y WHERE x.ID = y.ID This has the added benefit of taking the place of the 'WHERE 0=0' line we recently discussed.What are the benefits of one form of inner join over the other or is it just today's SQL fashion trend? It's hardly a fashion trend - it's the law (ANSI SQL 92 specification). Most modern databases support explicit JOINs. It provides two benefits. Jochem mentioned that it separates filters from join conditions. As a byproduct of this, it prevents unintentional Cartesian products or cross joins, which can easily happen when you perform joins in a WHERE clause. 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] [Donations and Support]
