[ic] Does ORDER BY rand() work in sql query ?

[Kevin Walsh]

> 
> I am able to do a mySQL query via command line mysql client on the server
> as "select * from products
>      where PurchasedZip LIKE 10101  ORDER BY rand() LIMIT 500"
> and I can do the same search in IC with no problem if I use the following
> form with the exception of the "rand()" sorting:
> 
>  <form action="[process-search]" method="post">
>  <input type="hidden" name="mv_searchtype" value="sql">
>  <input type="hidden" name="mv_matchlimit" value="500">
>  <input type="text" size="20" name="searchzip" value="">
>  <input type="hidden" name="mv_sql_query" value="select * from products
>      where PurchasedZip LIKE searchzip ORDER BY rand() LIMIT 500">
>  <input type="submit" name="mv-todo" value="Search">
>  </FORM>
> 
> If I remove the "ORDER BY rand()" its perfect. If I leave it in the
> results page shows "BAD_SQL" and the error.log from the account shows
> the errors below:
> 
> 
> xxx.xxx.xxx.xxx zC2YzcLs:xxx.xxx.xxx.xxx - [28/June/2002:15:55:05 -0400]
> localflorist
> /cgi-bin/localflorist/search.html SQL query failed: Bad SQL statement:
> Parse error
> near () LIMIT 500 at /usr/local/interchange/lib/Vend/Scan.pm line 586.
> >
> > Query was: select * from products where PurchasedZip LIKE searchzip
> ORDER BY rand()
> LIMIT 500 at /usr/local/interchange/lib/Vend/Interpolate.pm line 3453
> >
> > query was: select * from products where PurchasedZip LIKE searchzip
> ORDER BY rand()
> LIMIT 500
> 
> Any idea if its something to do with the rand() order statement itself or
> something about IC trying to get random info from the server or what?.
> I am running IC 4.8.3 , FreeBSD 4.3 and Apache 1.3.19.
> 
The "mv_sql_query" does not send the "SQL" directly to the database
server for processing.  If it did then anyone would be able to create
a URI to perform undesirable actions such as "DELETE FROM products".

Instead of this, Interchange does a "SELECT * FROM yourtable" and
then parses the results using the SQL::Statement module and your
"SQL". I'm quoting "SQL" here because the SQL::Statement module is
not a full SQL parser - as you have found.

If you want to use SQL then you should take a look at the [query]
tag.

-- 
   _/   _/  _/_/_/_/  _/    _/  _/_/_/  _/    _/
  _/_/_/   _/_/      _/    _/    _/    _/_/  _/   K e v i n   W a l s h
 _/ _/    _/          _/ _/     _/    _/  _/_/    kevin@cursor.biz
_/   _/  _/_/_/_/      _/    _/_/_/  _/    _/