"Stephen R. van den Berg" <[email protected]> writes:
> Michael A. Patton wrote:
>> <if variable="form.id">
>> <if expr='STRING(INT("&form.id:mysql;")) == "&form.id:mysql;"'>
>
> As you already suspected :mysql encoding is wrong here.
> I'd say you'd need &form.id:pike; instead (for both).
OK, I'll fix that... But, I guess I was really arguing for bringing
back the page of documentation that David Hunter posted and then
improving it to say not only _which_ characters are special in each
encoding, but _how_ they are encoded. That would make it clearer which
one(s) apply in a given case.
>> <set variable="var.title"><trim><sqltable query='
>> select prefix, title from item where id="&form.id:mysql;"
>> ' ascii='yes' /></trim></set>
>
> Several remarks:
> a. Using sqltable/ascii nested in a trim is awkward and inefficient.
But, WHY? As you'll see below, I still need the trim, and it would seem
to me that a single query using the output directly ought to be more
efficient than needing the overhead for the implied loop of an emit
(even if I already know there'll only be at most one match since the
column "id" is a unique non-null key). Also, I _like_ the cascaded
tests, because they generate better (i.e. more specific) error reports.
> b. Using " string delimiters in SQL is non-standard SQL and could result
> in security issues in the future if your SQL server is MySQL.
OK, I just checked the MySQL docs and I seem to have had that backward.
Probably a confusion from one of the other 50 some odd languages I've
learned over the years... But going back and interchanging all the '
and " chars throughout the site that need it will be a PITA. Something
to try and write an emacs command or macro for...oh fun, back to a
different language I haven't used in a couple of months...
> c. Not using bindings in the query is needlessly inefficient and increases
> the chance for new security leaks for no good reason.
I don't understand bindings. Someone else mentioned them as well. But
before this the only mention I'd seen of them (in all of the Roxen docs
and the MySQL language docs) was the comment that they could be used to
get past an Oracle restriction (which I ignored because I'm using the
MySQL that came with Roxen and not Oracle.) What do they really do?
> d. Why not add a LIMIT 1 to the SQL query to help the database
> evaluator?
Why is this going to help? The key I'm using is already declared
unique, so "there can be only one" (to quote "Highlander" :-) and MySQL
should already know that.
> Why not use something more straightforward/efficient/secure like this:
I certainly don't see it as more straightforward, it seems to be looping
for no good reason over a query of one result. I'm not sure I believe
the efficiency argument. And the security difference is really
non-existant. And besides, your example doesn't work...
> <emit source="sql" query="
> SELECT prefix, title
> FROM item
> WHERE id=:id
> LIMIT 1"
> bindings="id=form.id">
> <set variable="var.title" value="&_.prefix; &_.title;" />
> </emit>
> <else>
> No entry found
> </else>
This gets the wrong answer most of the time, because prefix is usually
empty (it's the article that may be at the start of the title but is not
used in sorting). The original point of the trim was to eliminate that
space between prefix and title when the prefix is empty. Otherwise you
get things like:
The book " Starwars" is...
Originally the trim was just in that use, but since I now use the title
in several places that have puctuation like that, it seemed best to just
always trim it... So, for that to work, you'd still need a trim:
<set variable="var.title"><trim>&_.prefix; &_.title;</trim></set>
or something equivalent...
-MAP
