Re: writing insert query sql in coldfusion
this is very logical advice, thanks very much, ill be sure to change our whole application to use the cfqueryparam tag thanks again ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297203 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
A lot depends on what level of access you have to the database. I have full control over all of mine so it never impacted me. I find I can really up the data validation, type checking, and speed by taking advantage of the oracle or sql server proc capability. I find my CF code is cleaner since I like to do a lot of data validation -- much easier to do on the back end and I can validate things natively that would take a lot of CF code to do. Not to sure about portability. I've never had that issue. I can move all my stored procs from on server to another in an instant. Maybe my preferences stem from the fact that I mostly use CF (via CFCs) as a middle tier between Flex and the database and to handle files. I suspect you all are much more into the nuances than I ! - Original Message - From: "Crow T. Robot" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 21, 2008 12:26 PM Subject: Re: writing insert query sql in coldfusion > To play Devil's advocate here...you lose a lot of portability & code > readability with SP's. I personally only use them when it makes sense > (speed or DB-only functionality - DTS comes to mind here) to do so. > > On Jan 21, 2008 12:16 PM, Craigsell <[EMAIL PROTECTED]> wrote: > >> And to further emphasize that -- I would encourage the use of stored >> procedures whenever possible. I've been using Oracle stored procdures >> with >> CF for years with great success. You all the benefits of the >> CFQUERYPARAM >> and you let the database do what it does best -- process data. >> >> Warren Koch >> >> - Original Message - >> From: "Dave Watts" <[EMAIL PROTECTED]> >> To: "CF-Talk" >> Sent: Monday, January 21, 2008 12:09 PM >> Subject: RE: writing insert query sql in coldfusion >> >> >> >> > out of interest what is the difference in using the >> >> > cfqueryparam and not? >> >> >> >> Well, the biggest reason is that it provides significant >> >> protection against SQL injection attacks. It basically does >> >> in-line data validation. >> > >> > Ben (and everyone else who's posted) is right that you should use this. >> > It's >> > worth pointing out, though, that Ben has listed two different things >> > above, >> > and those things are separate. CFQUERYPARAM separates data values from >> > executable SQL code, and that's how it prevents SQL injection attacks. >> > This >> > isn't "significant protection", it's complete protection. By separating >> > data >> > values from executable code, you are clearly telling the database that >> > data >> > values cannot be executed, so it doesn't matter whether they contain >> > SQL >> > commands - they simply won't be executed. >> > >> > Almost as a side effect, you get data validation before you send your >> data >> > to the database. >> > >> > Dave Watts, CTO, Fig Leaf Software >> > http://www.figleaf.com/ >> > >> > Fig Leaf Software provides the highest caliber vendor-authorized >> > instruction at our training centers in Washington DC, Atlanta, >> > Chicago, Baltimore, Northern Virginia, or on-site at your location. >> > Visit http://training.figleaf.com/ for more information! >> > >> > >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297004 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing insert query sql in coldfusion
To play Devil's advocate here...you lose a lot of portability & code readability with SP's. I personally only use them when it makes sense (speed or DB-only functionality - DTS comes to mind here) to do so. On Jan 21, 2008 12:16 PM, Craigsell <[EMAIL PROTECTED]> wrote: > And to further emphasize that -- I would encourage the use of stored > procedures whenever possible. I've been using Oracle stored procdures > with > CF for years with great success. You all the benefits of the CFQUERYPARAM > and you let the database do what it does best -- process data. > > Warren Koch > > - Original Message - > From: "Dave Watts" <[EMAIL PROTECTED]> > To: "CF-Talk" > Sent: Monday, January 21, 2008 12:09 PM > Subject: RE: writing insert query sql in coldfusion > > > >> > out of interest what is the difference in using the > >> > cfqueryparam and not? > >> > >> Well, the biggest reason is that it provides significant > >> protection against SQL injection attacks. It basically does > >> in-line data validation. > > > > Ben (and everyone else who's posted) is right that you should use this. > > It's > > worth pointing out, though, that Ben has listed two different things > > above, > > and those things are separate. CFQUERYPARAM separates data values from > > executable SQL code, and that's how it prevents SQL injection attacks. > > This > > isn't "significant protection", it's complete protection. By separating > > data > > values from executable code, you are clearly telling the database that > > data > > values cannot be executed, so it doesn't matter whether they contain SQL > > commands - they simply won't be executed. > > > > Almost as a side effect, you get data validation before you send your > data > > to the database. > > > > Dave Watts, CTO, Fig Leaf Software > > http://www.figleaf.com/ > > > > Fig Leaf Software provides the highest caliber vendor-authorized > > instruction at our training centers in Washington DC, Atlanta, > > Chicago, Baltimore, Northern Virginia, or on-site at your location. > > Visit http://training.figleaf.com/ for more information! > > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296988 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
And to further emphasize that -- I would encourage the use of stored procedures whenever possible. I've been using Oracle stored procdures with CF for years with great success. You all the benefits of the CFQUERYPARAM and you let the database do what it does best -- process data. Warren Koch - Original Message - From: "Dave Watts" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 21, 2008 12:09 PM Subject: RE: writing insert query sql in coldfusion >> > out of interest what is the difference in using the >> > cfqueryparam and not? >> >> Well, the biggest reason is that it provides significant >> protection against SQL injection attacks. It basically does >> in-line data validation. > > Ben (and everyone else who's posted) is right that you should use this. > It's > worth pointing out, though, that Ben has listed two different things > above, > and those things are separate. CFQUERYPARAM separates data values from > executable SQL code, and that's how it prevents SQL injection attacks. > This > isn't "significant protection", it's complete protection. By separating > data > values from executable code, you are clearly telling the database that > data > values cannot be executed, so it doesn't matter whether they contain SQL > commands - they simply won't be executed. > > Almost as a side effect, you get data validation before you send your data > to the database. > > Dave Watts, CTO, Fig Leaf Software > http://www.figleaf.com/ > > Fig Leaf Software provides the highest caliber vendor-authorized > instruction at our training centers in Washington DC, Atlanta, > Chicago, Baltimore, Northern Virginia, or on-site at your location. > Visit http://training.figleaf.com/ for more information! > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296985 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: writing insert query sql in coldfusion
> > out of interest what is the difference in using the > > cfqueryparam and not? > > Well, the biggest reason is that it provides significant > protection against SQL injection attacks. It basically does > in-line data validation. Ben (and everyone else who's posted) is right that you should use this. It's worth pointing out, though, that Ben has listed two different things above, and those things are separate. CFQUERYPARAM separates data values from executable SQL code, and that's how it prevents SQL injection attacks. This isn't "significant protection", it's complete protection. By separating data values from executable code, you are clearly telling the database that data values cannot be executed, so it doesn't matter whether they contain SQL commands - they simply won't be executed. Almost as a side effect, you get data validation before you send your data to the database. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296983 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing insert query sql in coldfusion
Well, the biggest reason is that it provides significant protection against SQL injection attacks. It basically does in-line data validation. There have been several discussions here on the list of the various benefits, so a quick search at the HoF site should give you a good idea about it. --Ben Doom Richard White wrote: >> Also, alot of people will advise you to use cfqueryparam (me included), so >> the code would look like: > > out of interest what is the difference in using the cfqueryparam and not? > > thanks > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296977 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
On Jan 21, 2008 7:58 AM, Richard White <[EMAIL PROTECTED]> wrote: > >Also, alot of people will advise you to use cfqueryparam (me included), so > >the code would look like: > > out of interest what is the difference in using the cfqueryparam and not? http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html -- "Scientists tell us that the fastest animal on earth, with a top speed of 120 feet per second, is a cow that has been dropped out of a helicopter." - Dave Barry ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296976 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing insert query sql in coldfusion
>Also, alot of people will advise you to use cfqueryparam (me included), so >the code would look like: out of interest what is the difference in using the cfqueryparam and not? thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296975 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
> password="#dbPassword#"> > insert into testTable(testID,data1,data2) > values ( , > , > ) > oh i see, no there isnt a good reason except i wasnt aware of it this way :) thanks ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296974 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
thanks very much for all your help, i didnt even realise i could use the has signs inside a string like that so your right i can cut out alot of the concatenation and also thanks for the preservesinglequotes() it works fine thanks again for your help. i can now change alot of my code to take out of all of the concatenation :) thanks >>>I'm pretty sure you don't need to use all that . > >But he still needs preserveSingleQuotes. > >-- >___ >REUSE CODE! Use custom tags; >See http://www.contentbox.com/claude/customtags/tagstore.cfm >(Please send any spam to this address: [EMAIL PROTECTED]) >Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296973 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
>>I'm pretty sure you don't need to use all that concatention. But he still needs preserveSingleQuotes. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296972 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: writing insert query sql in coldfusion
> > I'm pretty sure you don't need to use all that concatention. Try: > > > > Warren Koch Also, is there a very good reason why you don't build the query string inside your cfquery tags? i.e. insert into testTable(testID,data1,data2) values (#testIDValue#,'#data1Value#','#data2Value#') Also, alot of people will advise you to use cfqueryparam (me included), so the code would look like: insert into testTable(testID,data1,data2) values ( , , ) -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296971 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: writing insert query sql in coldfusion
I'm pretty sure you don't need to use all that concatention. Try: Warren Koch - Original Message - From: "Richard White" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Monday, January 21, 2008 4:42 AM Subject: writing insert query sql in coldfusion > Hi, i have a very strange error occuring when trying to build an insert > query in coldfusion then trying to run it in a query > > i have an app that manipulates alot of sql code. it builds tables and > inserts values into them. i have got it building tables fine except when i > try to run an insert code it tells me that there is an sql syntax. > > this is what i am doing. > > testIDValue & ",'" & data1Value & "','" & data2Value & "');" > > note that the data1 and data2 columns are varchar and the testID is > bigint, so i have had to enclose the data1 and data2 values with quotes: ' > > then i try to run the code by the following: > > insert into testTable(testID,data1,data2) values (123,'a','b'); > > which is fine as if i put that directly into sql it goes in with no > problems. but i have noticed that when it is outputting it in the query it > is actually trying to put 2 of the single quotes together on each side as > follows: > > insert into testTable(testID,data1,data2) values (123,''a'',''b''); > > which of course is throwing an error saying i have a problem in my sql > syntax > > does anyone know why this is occuring? it would be ok if they put a double > quote on each side of the string values but it isnt it is putting two of > the single quote marks together > > does anyone have any ideas on how i can get around this? > > thanks very much for your help > > password="#dbPassword#"> > #sqlQuery# > > > the problem is if i cfoutput the code it displays as follows: > > > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296969 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: writing insert query sql in coldfusion
Take a look at PreserveSingleQuotes(). That should fix things. Adrian http://www.adrianlynch.co.uk/ -Original Message- From: Richard White Sent: 21 January 2008 10:43 To: CF-Talk Subject: writing insert query sql in coldfusion Hi, i have a very strange error occuring when trying to build an insert query in coldfusion then trying to run it in a query i have an app that manipulates alot of sql code. it builds tables and inserts values into them. i have got it building tables fine except when i try to run an insert code it tells me that there is an sql syntax. this is what i am doing. #sqlQuery# the problem is if i cfoutput the code it displays as follows: ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296967 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
