SQL Updates Revisited

Small problem...

The sql updates i have coded word fine, but there seems to be a problem
with Date fields. The access database has a field configured for
LongDate. Smalltalk code is as follows:

| aStream |
aStream := WriteStream on: String new.
aStream nextPutAll: 'update customers set '; space;
aStream nextPutAll: 'First_Name=', (anObj firstName printString);
space;
aStream nextPutAll: ', Last_Name=', (anObj lastName printString);
space;
aStream nextPutAll: ', Birth_Date=', (anObj birthDate printString);
space;
aStream nextPutAll: 'where CustomerId=', (anObj customerId
printString).

^aStream contents

The problem is with the birthDate. Clear to me know that one can't just
use printString to encode it onto the stream as the API layer raises an
error. Is there some other way of converting the date value? Still
looking but haven't found anything just yet. This is probably simple
and I haven't looked in the right location in the image for the answer.
Thanks,

Pax

Pax,

> The problem is with the birthDate. Clear to me know that one can't just
> use printString to encode it onto the stream as the API layer raises an
> error. Is there some other way of converting the date value? Still
> looking but haven't found anything just yet. This is probably simple
> and I haven't looked in the right location in the image for the answer.

There is a standard format for expressing dates via ODBC:

{d'yyyy-MM-dd'}

The following method will accomplish this for you (implement in Date):

writeSQLOn: aStream

    aStream
        nextPut: ${;
        nextPut: $d;
        nextPut: $'.
    self printOn: aStream format: 'yyyy-MM-dd'.
    aStream
        nextPut: $';
        nextPut: $}

This is taken from my ReStore relational database package, depending on what
you're doing you may find it useful.

Regards,

John Aspinall
Solutions Software
http://www.solutionsoft.co.uk/restore

John,

thanks bud! Implemented your suggestion and it works great! I performed
some more research and it appear the format you have in the example is
the International (ISO) Format. Other formats were listed such as USA
(mm/dd/yyyy), EUR (dd.mm.yyyy) etc...

Can you explain the format string: {d'yyyy-MM-dd'}? More specifically,
the leading "d".

Looks like I can do the same thing for the time formats as well.
Restore looks great. A lot better than GLORP (seems bloated). Your
price is reasonable and I may just invest in your framework.
Thanks,

Pax

Pax wrote:

> aStream := WriteStream on: String new.
> aStream nextPutAll: 'update customers set '; space;
> aStream nextPutAll: 'First_Name=', (anObj firstName printString);
> space;
> aStream nextPutAll: ', Last_Name=', (anObj lastName printString);
> space;

If you have a customer called
    John O'Connor
then this will break.

If you have a customer called[*]:
    John' ; delete * from customers; --'
then it'll break /badly/.

If you use parameterised statements then you'll avoid both risks, and I think
date problem will be solved too.

/Never/ assemble SQL statement on-the-fly from strings supplied by any external
agency.

(No smilie, this is serious.)

    -- chris

([*] I've never attempted SQL-injection before, so I may have the attack string
a bit wrong.  You get the idea anyway...)

John Aspinall wrote:

Last month's thread 'Postgres ReStore quiestion' discovered that the PostgreSQL
ODBC driver expects a space between the {d and the date string.  I don't know
if its generally true that other drivers will (or ought to) accept the space,
but if so then adding it to the generic #writeSQLOn: method might be a good
idea.

BTW, for anyone who's interested but hasn't noticed.  PostgreSQL 8 is now out,
and features a Windows-native implementation.

    -- chris

> The problem is with the birthDate. Clear to me know that one can't just
> use printString to encode it onto the stream as the API layer raises an
> error. Is there some other way of converting the date value? Still
> looking but haven't found anything just yet. This is probably simple
> and I haven't looked in the right location in the image for the answer.
> Thanks,

I don't think the answer is in the image, unless you prepare statements
which tends to do a better job of either using, or perhaps just
bypassing(??) SQL encodings.  AFAIK, what you need to do is format
timestamps (maybe they call it date-time??), something like

    {ts '2005-01-30 14:08:58'}

I list the time as midnight and simply ignore it for dates.  Not
elegant, but we are dealing with relational databases... :)

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Chris,

> If you have a customer called
>     John O'Connor
> then this will break.
>
> If you have a customer called[*]:
>     John' ; delete * from customers; --'
> then it'll break /badly/.

Thanks for raising this concern!


> If you use parameterised statements then you'll avoid both risks, and I think
> date problem will be solved too.

Parameterizing is not necessarily the only way around it.  At least with
MySQL, I discovered escape sequences that handle most (if not all)
situations like this.  For your attack string, the escaped form is as
follows:

    "John\' ; delete * from customers; --\'"

I _think_ it would be safe to send on to MyODBC.

For Access, I think it's

       'John'' ; delete * from customers; --'''

I never did figure out how to escape $| for Access; I do not recall
whether or not I've tried it with MySQL.


> /Never/ assemble SQL statement on-the-fly from strings supplied by any external
> agency.
>
> (No smilie, this is serious.)

I wonder how many "enterprise systems" would fall victim to an attack
like this.

Have a good one,

Bill

--
Wilhelm K. Schwab, Ph.D.
[hidden email]

Pax,

> Can you explain the format string: {d'yyyy-MM-dd'}? More specifically,
> the leading "d".

I think the "d" just means "date". There's similar formats for Time and
Timestamp:

{t 'hh:mm:ss.mmmmmm'}
{ts 'yyyy-MM-dd hh:mm:ss'}

> Looks like I can do the same thing for the time formats as well.
> Restore looks great. A lot better than GLORP (seems bloated).

In the interests of professional courtesy, I feel obliged to point out that
GLORP is targetted differently to ReStore - GLORP provides a framework for
interfacing to existing databases, and thus requires more flexibility (hence the
"bloat") than ReStore, which has the luxury of definining its own
ReStore-friendly schema.

> Your price is reasonable and I may just invest in your framework.

That's good to know... there's a free 1-month trial if you just want to dip your
toe in the water.

Best regards,

John Aspinall
Solutions Software

Chris,

> Last month's thread 'Postgres ReStore quiestion' discovered that the
PostgreSQL
> ODBC driver expects a space between the {d and the date string.  I don't know
> if its generally true that other drivers will (or ought to) accept the space,
> but if so then adding it to the generic #writeSQLOn: method might be a good
> idea.
>
> BTW, for anyone who's interested but hasn't noticed.  PostgreSQL 8 is now out,
> and features a Windows-native implementation.

Thanks for the tips, particularly about Postgres 8 - that will make testing a
lot easier!

Best regards,

John Aspinall
Solutions Software

John Aspinall escribió:

>>Looks like I can do the same thing for the time formats as well.
>>Restore looks great. A lot better than GLORP (seems bloated).

> In the interests of professional courtesy, I feel obliged to point out that
> GLORP is targetted differently to ReStore - GLORP provides a framework for
> interfacing to existing databases, and thus requires more flexibility (hence the
> "bloat") than ReStore, which has the luxury of definining its own
> ReStore-friendly schema.

Besides the bloat of GLORP, i've got scared in the approaching to it.

What isn't over there is some kind of benchmark, or profilings of
how performant is any of those. Somebody using both solutions may
post here some clues.

GLORP seemed to be "too much" for my actual needings.
I've requested a trial of ReStore a couple of months ago, but never
got an answer.

So... in the meantime, i've started building my own, which is
luxurious than ReStore, because its tailor-made to my way of work,
and my actual needings. The future will say...
  :-)

Best regards.

--
Esteban A. Maringolo
[hidden email]

Esteban,

> I've requested a trial of ReStore a couple of months ago, but never
> got an answer.

Sorry to hear that; I'll have to put it down to an over-aggresive spam filter.
I've now mailed you a copy of the trial version.

Good luck with rolling your own solution.

Best regards,

John Aspinall
Solutions Software

Bill,

> > If you use parameterised statements then you'll avoid both risks, and I
> > think date problem will be solved too.
>
> Parameterizing is not necessarily the only way around it.  At least with
> MySQL, I discovered escape sequences that handle most (if not all)
> situations like this.

I really recommend that you (or anyone) never try to go down that route.
Don't even think about it.  You'll end up spending ages trying to work out how
to "fool" the parser.  You'll risk producing complicated bugs of your own.
You'll almost certainly miss some cases, and/or make yourself dependent bugs in
the parser, and/or make yourself more dependent than necessary on the DB
itself.

Using prepared statements is safer and much, much, easier.


> > /Never/ assemble SQL statement on-the-fly from strings supplied by any
> > external agency.
> >
> > (No smilie, this is serious.)
>
> I wonder how many "enterprise systems" would fall victim to an attack
> like this.

The security community (of which I am only a very distant observer) seems to
think that such flaws are common and an important source of cracking
risks/opportunities.  I have no idea how any actual flaws of this nature are
exploited.  My /guess/ is that it happens quite often; from my own experience,
few programmers can be persuaded not to construct SQL by concatenating unsafe
strings, and the results of their efforts are quite often exposed to the world.

It would be interesting, if String literals were genuinely immutable objects
(as I think is the case in VW these days), to make the ODBC interface reject
any query string that was mutable...

    -- chris

Chris,

>>Parameterizing is not necessarily the only way around it.  At least with
>>MySQL, I discovered escape sequences that handle most (if not all)
>>situations like this.
>
> I really recommend that you (or anyone) never try to go down that route.
> Don't even think about it.  

It's long since done, and has served well.  However, your concerns are
noted and very much appreciated.


 > You'll end up spending ages trying to work out how
> to "fool" the parser.

That was very true with Microsoft databases and drivers; it has been
seemless with MySQL - read the docs, did what they said, everything worked.

As I reported a while back, I had some problems with "lockups" that
turned out to be horribly inefficient queries, but that all made sense
when I got to the bottom of it.


 > You'll risk producing complicated bugs of your own.
> You'll almost certainly miss some cases, and/or make yourself dependent bugs in
> the parser, and/or make yourself more dependent than necessary on the DB
> itself.
>
> Using prepared statements is safer and much, much, easier.

Agreed except on the latter point; though I admit I have the capability.
    I built it a while back in the hopes of performance boost; instead
of a couple or so powers of ten, I got a little better than one power of
two :(  The time saved parsing the statements is shifted to
setting/removing the parameter values (or something like that, it's been
a while since I looked at profiles of it).

My results might be diappointing in large part because my queries are
seldom very complicated.  The ones that are ugly are ones that I create
myself.


Have a good one,

Bill


--
Wilhelm K. Schwab, Ph.D.
[hidden email]