CF with some sql 1

[mochalova]

Hello,

This might be for someone who knows CF as well as sql.


I have a list coming in from a text box called ID:
SOH01123, SOH01146,SOH01141, SOH0118


With these values I have to run a select statement:
Select * from my table
where (UserID IN ('#form.id#'))

When I do this, sql wont work because it shows in sql as:
SELECT * from my table
WHERE (UserID IN ('SOH01123, SOH01146,SOH01141, SOH01185 '))

Surely this didn't work because it should be something like this:
WHERE (UserID IN ('SOH01123','SOH01146','SOH01141','SOH01185'))

To achieve the right where statement, I have to do a replace in CF to insert the ' in the right places.

Which I do like this:
<cfset myList=ListChangeDelims(MyList,"','",',')>

That replace worked perfectly when I did a cfoutput of mylist.

However my sql statement is now a mess. It looks like
SELECT * from my table
WHERE (UserID IN ('SOH01123'','' SOH01146'',''SOH01141'','' SOH01185 '))

Any help would be great.

Thanks

 

[LyndonOHRC]

Mocha,
You won't need the ListChangeDelims as that was not the problem.

Try:

SELECT * from my table
WHERE UserID IN (<cfqueryparam value="#Form.myTextBox#" list="true" cfsqltype="cf_sql_varchar">)

Lyndon

---People Remember about 10% of what you say ---They never forget how you made them feel. Covey

 

[cfSearching]

Ah, I see you were using the values in a SQL statement after all

However my sql statement is now a mess. It looks like ...

Yes. The reason that happens is that ColdFusion automatically escapes the single quotes _within_ your values. ie Converts one (1) quote into two (2) quotes. That is done deliberately as a protection against sql injection.

The best option is to use cfqueryparam as Lyndon suggested. Then you do not need to worry about quotes at all. Plus, it is good to get into the habit of always using cfqueryparam anyway.

----------------------------------

http://cfsearching.blogspot.com/

 

[mochalova]

Thank you both so very much.

Lyndon, you have saved the day one more time.

Thanks