IN operator

gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

Is the IN operator available in Webinator 2.5? If not, is there an easy way to achieve the same effect?
User avatar
John
Site Admin
Posts: 2580
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

IN operator

Post by John »

The SQL in Webinator 2.5 does understand IN. What are you trying to achieve?
John Turnbull
Thunderstone Software
gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

I want to retrieve several specific records by using the -unique id hash, something like "select Title, Meta, id from html where id IN(78227130186c6924,516a512811223548)". I haven't tried it yet, just making sure it will work.
User avatar
John
Site Admin
Posts: 2580
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

IN operator

Post by John »

You would need single quotes around the values, but it will work.
John Turnbull
Thunderstone Software
gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

Well I tried it and I get a "garbled time in the function month" error.
gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

Ok, I've done a bit more troubleshooting and it looks like the problem has to do with passing a variable through the query string. I'm passing a variable "rid" like so: script?db=db&cmd=list&rid='237841204f4e3428','41122840106a4930'

The script fails with the error above when I try:
<SQL "select Title, Meta, id from html where id IN ($rid)">

But works fine when I hardcode the values:
<SQL "select Title, Meta, id from html where id IN ('237841204f4e3428','41122840106a4930')">

The $rid variable looks fine when I output it, so what am I doing wrong?
User avatar
John
Site Admin
Posts: 2580
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

IN operator

Post by John »

An inline variable can only represent a single value, and would not need the single quotes. If you write the query as:

<SQL "select Title, Meta, id from html where id IN (" $rid ")">

it should work, however you do run the risk of SQL injection, and so should validate the value of $rid.
John Turnbull
Thunderstone Software
gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

Unfortunately that breaks the whole script. I'm guessing it's the nested double quotes. I tried variations using single quotes but no luck. Note taken on the SQL injection. Any other ideas? Would something like the following work?

script?rid=237841204f4e3428,41122840106a4930
<split "," $rid></split>
<SQL "select Title, Meta, id from html where id IN ($ret)">
gzip
Posts: 9
Joined: Tue Jun 14, 2005 2:45 pm

IN operator

Post by gzip »

?
User avatar
John
Site Admin
Posts: 2580
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

IN operator

Post by John »

No, the above wouldn't work. What is the error you were getting with the $rid as a separate component? That should work.
John Turnbull
Thunderstone Software
Post Reply