Avoiding SQL injections with squeak / seaside / mysql driver

Hello,

I hope this is the right place to ask the question.
I'm using squeak / seaside and the mysql driver to implement a web site
with database support.

I wish to avoid SQL injections from user input.

Does anyone know if there is something already in Squeak to do this
(namely escaping quotes in user input) ?

Thanks a lot.

Vincent Girard-Reydet
_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

One thing you should do is make sure you never construct SQL by
concatenation, but rather always use bound values (if Squeak's db
interface supports them, of course).

Cheers,

-Boris

--
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[hidden email]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Vincent
Girard-Reydet
Sent: Thursday, October 19, 2006 9:46 AM
To: [hidden email]
Subject: [Seaside] Avoiding SQL injections with squeak / seaside /
mysqldriver

Hello,

I hope this is the right place to ask the question.
I'm using squeak / seaside and the mysql driver to implement a web site
with database support.

I wish to avoid SQL injections from user input.

Does anyone know if there is something already in Squeak to do this
(namely escaping quotes in user input) ?

Thanks a lot.

Vincent Girard-Reydet
_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

If you were using Postgres, I might suggest that you use the ROE  
library, which abstracts the SQL generation so that you're never  
doing any concatenation of any kind.  However, it relies heavily on  
sub-selects, so it doesn't play nicely with MySQL.

Avi

On Oct 19, 2006, at 9:51 AM, Boris Popov wrote:

_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

Vincent,

My image has String asEscapedSql it may or may not be in your image  
but it is easy to add. Assuming you don't use "s to surround strings  
the following should work.

asEscapedSql
        ^String streamContents: [ :stream |
                self do: [ :char |
                        (#($' $\) includes: char)
                                ifTrue: [ stream nextPut: char ].
                        stream nextPut: char ] ]

Just add that method to your string class and call it when putting  
strings into the database.

It will make ' look like '' and \ look like \\.

If you are interested in object to relational storage for MySQL or  
PostgreSQL have a look at REServe.

http://squeaksource.com/REServe.html

It allows you to store objects in a relational database without ever  
having to deal with SQL. It supports polymorphism, collections,  
Smalltalk enumeration like queering, and some other nice features.  
Currently it is not possible to map existing tables to objects but  
that should not be too difficult to do (it might even work now but I  
have never tried it).

Enjoy
Will

On Oct 19, 2006, at 12:46 PM, Vincent Girard-Reydet wrote:

_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

Very cool.  Is this how the ruby database stuff works?  I am tied up
with RecuranceRule right now, and after that a web site.  But when I get
those two things done I was considering doing a RoR type thing in
Seaside.  Of course it will be "seaside-esque" (i.e. everything is done
in the web, no config files, etc.  And styling is done with CSS instead
of hand written template yuckery).  So I need to know what database
thing will get me the closest to the ActiveRecord stuff they do (haven't
really looked at it yet).

Also, does your driver have any compiled components?  My preference
would be pure smalltalk drivers if possible.  But that wish is just
based on the belief that a binary component will mean possibly doing
something to the VM, or at a minimum having to worry about binary
compatibility.  If this stuff is completely painless in squeak somehow,
then I guess it wouldn't matter so much.

William Harford wrote:
_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

Hi William,

I don't have asEscapedSql in my image, but I will use the one you
provided to me? Thanks for your answer.

Vincent

William Harford wrote:
_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

On Oct 20, 2006, at 2:15 PM, Jason Johnson wrote:

> Very cool.  Is this how the ruby database stuff works?

I am not sure how the Ruby stuff works but you could do a CRUD frame  
work with REServe.

The idea with REServe is the database is created/maintained using  
your object model.

> I am tied up with RecuranceRule right now, and after that a web  
> site.  But when I get those two things done I was considering doing  
> a RoR type thing in Seaside.

I would be happy to help with the effort. I would suggest we don't  
try and copy RoR but try and address the same problems in a different  
way.

> Of course it will be "seaside-esque" (i.e. everything is done in  
> the web, no config files, etc.  And styling is done with CSS  
> instead of hand written template yuckery).  So I need to know what  
> database thing will get me the closest to the ActiveRecord stuff  
> they do (haven't really looked at it yet).

REServe would give you the data component. I would be cool if a  
programmer could simply define the interface and have the data model  
and database create themselves. It really should be quite easy to do  
with Seaside.

I have actually thought quite a lot about this and would love to get  
stared in the near future if time allows.

I would love to write a lot more on the subject but I am just to busy  
today. If you would like to start a dialog on the subject please do.  
I will jump in when time allows.

> Also, does your driver have any compiled components?

I use a slightly modified MySQL driver. And Yanni Chiu's PostgreSQL  
driver to interface with the databases. Those drivers are abstracted  
from REServe via the REServeMySQLDriver and REServePostgreSQLDriver  
classes.

Both the MySQL and PostgreSQL drivers are native Squeak.


Keep in touch.
Will


_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside

William Harford wrote:
I don't either to be honest.

>
>> I am tied up with RecuranceRule right now, and after that a web
>> site.  But when I get those two things done I was considering doing a
>> RoR type thing in Seaside.
>
> I would be happy to help with the effort. I would suggest we don't try
> and copy RoR but try and address the same problems in a different way.

I don't mean copy exactly.  I was just thinking of the features and make
them better at every point.  For example, with RoR (I did the tutorial a
little) you run some command to create the "application".  We could do
this from the web in seaside.  Then in RoR you define the database for
the new application in a config file.  This could also be done right in
the web page on seaside.  Then in RoR if you want to change how things
are displayed you have to get into template nastyness.  If we build on
top of Pier you can build the pages right inside the browser and not
have to do any template stuff.  For the database stuff we could make a
new kind of component for pier that integrates with the database a
little better.  The idea being, I think we would want to be able to
accomplish as much as possible without writting any code.  RoR gets a
little ways, but I think we could do better.

I don't know what web frame work shoppers look for, but if I needed to
do a CRUD set up, I would see it as a big win to have a frame work where
I can do everything right in the browser.  No command line stuff.  No
templates.  No lines of code.  Just do it all in the browser from the start.
Yea, my time is pretty limited at the moment as well.  But I think this
is really important, so if no one does it first, I will do what I can. :)
>> Also, does your driver have any compiled components?
>
> I use a slightly modified MySQL driver. And Yanni Chiu's PostgreSQL
> driver to interface with the databases. Those drivers are abstracted
> from REServe via the REServeMySQLDriver and REServePostgreSQLDriver
> classes.
>
> Both the MySQL and PostgreSQL drivers are native Squeak.
>

Wonderful.  After the system is made it could be just a simple squeakmap
entry that loads everything, and then our system would probably be the
quickest around to get from no where to a web site with database back end.
>
> Keep in touch.
> Will
>

Will do.  Thanks a lot for the information.
>
> _______________________________________________
> Seaside mailing list
> [hidden email]
> http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside
>

_______________________________________________
Seaside mailing list
[hidden email]
http://lists.squeakfoundation.org/cgi-bin/mailman/listinfo/seaside