Unknown Column Error

[teedoff]

I have several queries on my site. I have a search.cfm that queries my database, then for each records I have a link coded in on the #item# column of my records.

I also have the ability for users to go to "section" pages that have queries based on the category type stored in my database.

I say all this because, originally I was passing a URL parameter which was bulbID so that the link would open the bulbsDetail.cfm based on which bulbID was passed.

I changed that so that the actual item would be passed in the URL for search engine reasons. Now however when I click on any bulb item that is all numbers, for example...13564 there is not problem. The bulbsDetails.cfm page displays properly.

When I click on an item that has letters in it, for example 60K19/DL, I get an error saying Unknown Column '60K19/DL' in 'WHERE clause'

Here is my query in question:

<cfparam name="URL.item" type="any">
<cfquery name="rsDetails" datasource="rlbulbs">
SELECT a.*, b.brandID, b.brandName 
FROM rlbbulbs a, rlbbrand b 
WHERE a.brandID = b.brandID
AND a.item = #URL.item#
</cfquery>

Not sure whats happeneing. Thanks for any help!

[teedoff]

Ok wait..lol after spending about 2 hours on this....I needed to add single quotes around my URL.item. lol sorry post solved!

[arrgh]

AND a.item = #URL.item#

I hope you're using MS Access ....

(I can't believe I actually said that)

[teedoff]

I cant believe you'd suggest that!
lol
umm no, MySQL why??

[arrgh]

I cant believe you'd suggest that!
lol
umm no, MySQL why??

I know. It's terrible. But you're not using cfqueryparam :P Whatever you might say about MS Access (and I could say a lot) AFAIK it's not vulnerable to cfquery sql injection the way other db's like MySQL are.

[teedoff]

So instead of <cfparam> it's better to use <cfqueryparam>? It would be a snap to change my cf params.

[arrgh]

Those are two totally different things.

CFPARAM sets a default value for a variable if it doesn't exist (with optional type checking).

CFQUERYPARAM is used only inside cfqueries. Though not it's main function, it does indirectly help prevent sql injection by using bind variables and enforcing stronger type checking. Neither can be achieved w/cfparam. You just have to use cfqueryparam .. and use it everywhere! ;-)

<cfquery name="rsDetails" datasource="rlbulbs">
SELECT ... FROM ....
AND a.item = <cfqueryparam value="#URL.item#" cfsqltype="cf_sql_integer">
</cfquery>

[teedoff]

Ahh ok, well I wrote my update queries with that in mind. Like this:

<cfparam name="URL.id" default="1">
<cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "form1">
  <cfquery datasource="rlbulbs">   
    UPDATE rlbbulbs
SET 
item=<cfqueryparam value="#FORM.item#" cfsqltype="cf_sql_varchar" maxlength="255">
, brandID=<cfqueryparam value="#FORM.brandID#" cfsqltype="cf_sql_varchar">
, bulbDesc=<cfqueryparam value="#FORM.bulbDesc#" cfsqltype="cf_sql_varchar">
, cost=<cfqueryparam value="#FORM.cost#" cfsqltype="cf_sql_varchar">
, price=<cfqueryparam value="#FORM.price#" cfsqltype="cf_sql_varchar">
, qtyPerPrice=<cfqueryparam value="#FORM.qtyPerPrice#" cfsqltype="cf_sql_varchar">
, qtyInStock=<cfqueryparam value="#FORM.qtyInStock#" cfsqltype="cf_sql_varchar">
, wattage=<cfqueryparam value="#FORM.wattage#" cfsqltype="cf_sql_varchar" maxlength="255">
, voltage=<cfqueryparam value="#FORM.voltage#" cfsqltype="cf_sql_varchar" maxlength="255">
, acDC=<cfqueryparam value="#FORM.acDC#" cfsqltype="cf_sql_varchar" maxlength="255">
, base=<cfqueryparam value="#FORM.base#" cfsqltype="cf_sql_varchar" maxlength="255">
, glass=<cfqueryparam value="#FORM.glass#" cfsqltype="cf_sql_varchar" maxlength="255">
, filament=<cfqueryparam value="#FORM.filament#" cfsqltype="cf_sql_varchar" maxlength="255">
, avgLife=<cfqueryparam value="#FORM.avgLife#" cfsqltype="cf_sql_varchar" maxlength="255">
, beamAngle=<cfqueryparam value="#FORM.beamAngle#" cfsqltype="cf_sql_varchar" maxlength="255">
, oldPage= <cfqueryparam value="#FORM.oldPage#" cfsqltype="cf_sql_varchar" maxlength="255">
, keyWords=<cfqueryparam value="#FORM.keyWords#" cfsqltype="cf_sql_varchar" maxlength="2147483647">
, image1=<cfqueryparam value="#FORM.image1#" cfsqltype="cf_sql_varchar" maxlength="255">

WHERE bulbID=<cfqueryparam value="#FORM.bulbID#" cfsqltype="cf_sql_varchar">
  </cfquery>

didnt think I had to do that when the query was just a user defined search against the database. No data is being entered into the db

[arrgh]

Good. Just be sure to use the correct types or you'll have other problems. I doubt all of your columns are VARCHAR ;-) ID columns are probably integers, or some numeric type. Btw: What type is "keywords" ?

cfqueyparam data types
http://livedocs.adobe.com/coldfusion/8/Tags_p-q_18.html#1102474

[arrgh]

didnt think I had to do that when the query was just a user defined search against the database. No data is being entered into the db

Any time you're running sql against a db that uses user-supplied info, you should use cfqueryparam. Even with SELECT statements, crafty users could append bad stuff to the variables and make it do bad things.

[teedoff]

I see! Thanks for the info and I will definitely start reworking my queries! The ways ppl can come up with to wreak havoc and destroy ppl's work! lol

[arrgh]

Now if only they'd put all that energy and creativity towards good things, what a wonderful world it'd be ;-)

[teedoff]

lol true! But think of all the jobs in internet and network security that would be lost. People who's jobs are to continually come up with ways to secure things, as new ways are being developed to hack and intrude on things..lol It's the cycle of weblife.

[arrgh]

I'm sure we'd all find something else to do with our skills and time. Not that it's likely to happen any time soon ;-)

[teedoff]

btw, to asnwer your questions you posed before. No all my datatype are not varchar. I need to go back and change those. But, most of them are now char type. BrandID and bulbID is the only "unique" columns listed above that could be set as interger. Dont know why I didnt set it that way. I also set item as text because its not my primary key, as it contains duplicates sometimes, based on the same type bulb made by different brandnames, and letters and numbers as well.

The column "keywords" are set as longText in my database, as well as the column "bulbDesc", so I need to change that also. Keywords are for meta-tag keywords that my employer is still using for his site.

So yes I went back and looked at all my queries, and anywhere I had a user defined query, I added the cfqueryparam. Thanks for the heads up, I didnt know I needed to do that!

[arrgh]

Hmm... I'm confused about your column types.

most of them are now char type.

You mean literally CHAR type (not VARCHAR)? Just curious.. but why? I tend to use it sparingly ;) Mainly for single characters codes. CHAR fields can behave differently than VARCHAR when it comes to white space. I constantly forget that. Then end up driving myself crazy trying to figure out why some query isn't behaving the way I think it should.

also set item as text because its not my primary key

You mean the literal type TEXT (ie really big field)? That doesn't really make sense if you're going to limit it to 255 characters. At least I think that was the limit in your cfqueryparam.

I'm not sure about the data type choice for the other fields either ;)

[teedoff]

Not sure why for any of that...lol I just remember having quite a bit of trouble when I migrated my Access db over to MySQL. I think I read somewhere that varchar and char were similar, but that char was a bit faster for queries.

I also think I remember trying to set keywords datatype to varchar and or char, and kept getting erros saying something about data too long for that data type when I would try to insert data to that field.

Maybe I should go back and rework some of these and change them.

[arrgh]

Not sure about performance diff's with type CHAR. Like I said I rarely use it. But you should definitely understand how/if it will effect your queries. I tend to use CHAR only when I know the value will _always_ be a specific length. Otherwise, it makes more sense to use VARCHAR (variable length character ;-)

As far as very large fields, yeah you might need to select a larger type than varchar. But that wouldn't apply if you're limiting it to 255 characters. That's the one I was talking about. For values that might be _much_ larger, then yeah - TEXT would be appropriate. Though you should probably use cf_sql_longvarchar for them.

Maybe I should go back and rework some of these and change them.

Yeah, and make sure you've matched up the cfsqltypes correctly. While the wrong types might work too, you may as well use the right ones. Then you get the benefit of whatever type checking CF does in the background.

[teedoff]

Ok so if I set them to varchar instead of char, then I can delete the maxLength in my sql queries? Since varchar is variable length datatype, and the maxlength is set to
255, that kinda nullifies the varchar type correct?

[arrgh]

Ok so if I set them to varchar instead of char, then I can delete the maxLength in my sql queries? Since varchar is variable length datatype, and the maxlength is set to
255, that kinda nullifies the varchar type correct?

Yes and no. Varchars can have different limits in different dbs. In MS SQL 2005, the limit is 8000. So just because the limit is 255 in your db, doesn't mean CF knows that.

Using maxLength means CF will check the value's length before sending anything to the db. If it's too long CF will throw an error. If you DON'T use maxlength, CF will just send the value straight to your db,. If the string ends up being too long, you'll probably get a db error.

So if you're going to get an error either way, what's the difference? ;) Personally I think it's better to use maxLength. Then CF doesn't even bother attempting the db query if the value is too long. Translation: One less db query. Of course in reality it wouldn't happen that frequently, because you normally validate this kind of stuff on the client side first.