January 2015 - Pharo-dev - lists.pharo.org

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by stepharo

olivier I added http://www.squeaksource.com/PostgresV3/ and the link sent by stefan to your table. we should check if it is working in Pharo. Stef Le 10/1/15 10:46, olivier auverlot a Ã©crit : > SQL support is very poor in Pharo and many improvements are necessary. > PostgresV2 is actually probably the best solution available because it > is written only in Pharo and works on many platforms. The framework > does the work but it's a limited solution. > > Olivier ;-) > > 2015-01-10 10:09 GMT+01:00 stepharo <stepharo(a)free.fr > <mailto:stepharo@free.fr>>: > > May be this is the time to improve this package :) > I'm sorry I have no DB experience. > As part of the consortium agenda for this year we have > - better ffi > - better db support > > Stef > > Le 10/1/15 02:06, David Carlos Manuelda a Ã©crit : > > I've installed and tested PostgresV2 under pharo with the > following doits > > Gofer new > smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; > configuration; > load. > (#ConfigurationOfPostgresV2 asClass project version: '2.4') load > > While it works good, it is missing a very important feature > from both > security and performance point of view: The prepared statements. > > As a brief, prepared statements are parameterized SQL > statements that are > loaded ONCE per connection instead of sending the whole query > to DB every > time, and also, they are parameterized, so it completelly > prevents SQL > injection, as the parameters are automatically 'detected' and > scaped and/or > handled accordingly without allowing in any case a parameter > to alter the > SQL meaning, which can happen by using regular SQL queries > made by string > concatenation. > > I browsed the class and did not find any prepare: method nor > anything > similar. > > Also, you can give a name to a SQL sentence, which makes the > code much more > readable without messing too much logic with SQL commands and > string > concatenation. > > An example is as follows: (supposing we have an instance variable > connection, already initialized and connected via PGConnection > class) > > Instead of: > > self connection execute: 'SELECT data FROM mytable WHERE > name=''', anUser > userName, ''';'. > > Would be something like this: > self connection executePrepared: 'getUserData' with: anUser > userName. > > And another suggestion could be something like: > self connection prepare: 'getUserData' withSQL: 'SELECT data > FROM mytable > WHERE name=$1' > > Any plan for this to be implemented or any hint to other > PostgreSQL class > that already has it? > > David. > > P.S. I've written in my blog about this some time ago: > http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… > if someone finds it useful. > > > > > >

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by stepharo

Le 10/1/15 10:59, Stephan Eggermont a Ã©crit : > Stef wrote: >> May be this is the time to improve this package :) >> I'm sorry I have no DB experience. >> As part of the consortium agenda for this year we have >> - better ffi >> - better db support > Moving to Levente's PostgreSQLv3 and migrating that > might be a better idea. He has been using it in production > systems for a long time AFAIK. when I meant db support it is not limited to one implementation. Olivier Auverlot started to do a document listing all the solutions and their status. https://docs.google.com/spreadsheets/d/1uD6UFmwCJxAkOC9tlvdJsHtt10C0KHJ6Zp8… Add the information avout SQLv3 Now I do not get it. Did you port it to Pharo? > > The code mirror is very interesting. Porting it needs > someone with close access to Opal/old compiler knowledge. > The way it works now in Squeak looks ideal for someone > with a PostgreSQL background. Using a code browser > to edit plsql, and having a directly mirrored smalltalk > class for each table. > > http://forum.world.st/Status-of-PostgresV3-td4780110.html > I have verified that the connection works and that I can get > data out of postgres. > Missing: > - switch the code highlighter from smalltalk to one > for plsql in nautilus. > - connect to glorp > - (later) add a real plsql parser (petitsql?) > > Stephan > > > >

Jan. 10, 2015

Prepared Statements on PostgresV2?

by Stephan Eggermont

Stef wrote: >May be this is the time to improve this package :) >I'm sorry I have no DB experience. >As part of the consortium agenda for this year we have > - better ffi > - better db support Moving to Levente's PostgreSQLv3 and migrating that might be a better idea. He has been using it in production systems for a long time AFAIK. The code mirror is very interesting. Porting it needs someone with close access to Opal/old compiler knowledge. The way it works now in Squeak looks ideal for someone with a PostgreSQL background. Using a code browser to edit plsql, and having a directly mirrored smalltalk class for each table. http://forum.world.st/Status-of-PostgresV3-td4780110.html I have verified that the connection works and that I can get data out of postgres. Missing: - switch the code highlighter from smalltalk to one for plsql in nautilus. - connect to glorp - (later) add a real plsql parser (petitsql?) Stephan

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by olivier auverlot

SQL support is very poor in Pharo and many improvements are necessary. PostgresV2 is actually probably the best solution available because it is written only in Pharo and works on many platforms. The framework does the work but it's a limited solution. Olivier ;-) 2015-01-10 10:09 GMT+01:00 stepharo <stepharo(a)free.fr>: > May be this is the time to improve this package :) > I'm sorry I have no DB experience. > As part of the consortium agenda for this year we have > - better ffi > - better db support > > Stef > > Le 10/1/15 02:06, David Carlos Manuelda a Ã©crit : > > I've installed and tested PostgresV2 under pharo with the following doits >> >> Gofer new >> smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; >> configuration; >> load. >> (#ConfigurationOfPostgresV2 asClass project version: '2.4') load >> >> While it works good, it is missing a very important feature from both >> security and performance point of view: The prepared statements. >> >> As a brief, prepared statements are parameterized SQL statements that are >> loaded ONCE per connection instead of sending the whole query to DB every >> time, and also, they are parameterized, so it completelly prevents SQL >> injection, as the parameters are automatically 'detected' and scaped >> and/or >> handled accordingly without allowing in any case a parameter to alter the >> SQL meaning, which can happen by using regular SQL queries made by string >> concatenation. >> >> I browsed the class and did not find any prepare: method nor anything >> similar. >> >> Also, you can give a name to a SQL sentence, which makes the code much >> more >> readable without messing too much logic with SQL commands and string >> concatenation. >> >> An example is as follows: (supposing we have an instance variable >> connection, already initialized and connected via PGConnection class) >> >> Instead of: >> >> self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser >> userName, ''';'. >> >> Would be something like this: >> self connection executePrepared: 'getUserData' with: anUser userName. >> >> And another suggestion could be something like: >> self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable >> WHERE name=$1' >> >> Any plan for this to be implemented or any hint to other PostgreSQL class >> that already has it? >> >> David. >> >> P.S. I've written in my blog about this some time ago: >> http://stormbyte.blogspot.com.es/2012/06/programming-with- >> database-using.html if someone finds it useful. >> >> >> >> > >

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by stepharo

May be this is the time to improve this package :) I'm sorry I have no DB experience. As part of the consortium agenda for this year we have - better ffi - better db support Stef Le 10/1/15 02:06, David Carlos Manuelda a Ã©crit : > I've installed and tested PostgresV2 under pharo with the following doits > > Gofer new > smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; > configuration; > load. > (#ConfigurationOfPostgresV2 asClass project version: '2.4') load > > While it works good, it is missing a very important feature from both > security and performance point of view: The prepared statements. > > As a brief, prepared statements are parameterized SQL statements that are > loaded ONCE per connection instead of sending the whole query to DB every > time, and also, they are parameterized, so it completelly prevents SQL > injection, as the parameters are automatically 'detected' and scaped and/or > handled accordingly without allowing in any case a parameter to alter the > SQL meaning, which can happen by using regular SQL queries made by string > concatenation. > > I browsed the class and did not find any prepare: method nor anything > similar. > > Also, you can give a name to a SQL sentence, which makes the code much more > readable without messing too much logic with SQL commands and string > concatenation. > > An example is as follows: (supposing we have an instance variable > connection, already initialized and connected via PGConnection class) > > Instead of: > > self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser > userName, ''';'. > > Would be something like this: > self connection executePrepared: 'getUserData' with: anUser userName. > > And another suggestion could be something like: > self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable > WHERE name=$1' > > Any plan for this to be implemented or any hint to other PostgreSQL class > that already has it? > > David. > > P.S. I've written in my blog about this some time ago: > http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… if someone finds it useful. > > >

Jan. 10, 2015

Re: [Pharo-dev] Need a PC users to get the PDF of

by stepharo

Apparently this book is still on sold :( Le 10/1/15 09:52, stepharo a Ã©crit : > Hi guys > > I would like to add Smalltalk, Objects and Design to our free book > libraries and I do not have a pdf saver on mac for > google book > > https://books.google.fr/books?id=W8_Une9cbbgC&printsec=frontcover&num=15&hl… > > > Could one of you give a try? > > Thanks > > Stef > >

Jan. 10, 2015

Re: [Pharo-dev] Need a PC users to get the PDF of

by stepharo

The book is incomplete. I will probably scan my version if I have one. Le 10/1/15 09:52, stepharo a Ã©crit : > Hi guys > > I would like to add Smalltalk, Objects and Design to our free book > libraries and I do not have a pdf saver on mac for > google book > > https://books.google.fr/books?id=W8_Une9cbbgC&printsec=frontcover&num=15&hl… > > > Could one of you give a try? > > Thanks > > Stef > >

Jan. 10, 2015

Re: [Pharo-dev] [ANN] Rewritten EventRecorder (now based on Spec)

by stepharo

really nice! Le 9/1/15 23:37, Torsten Bergmann a Ã©crit : > Hi, > > I've rewritten, refactored, commented the Event Recorder. The most > visible feature is that the UI is now based on Spec to fit Pharo style. > > Currently it can record and replay events. Saving and loading > of events as *.tape files is still missing and (depending on time) > will be added as a next step. > > With this tool we have the ability to record and exchange actions > done in an image to reproduce bugs. The tool can also be used > for demo purposes. > > As it is already usable I made it available in the config browser > of Pharo 4. > > Attached is a screenshot, Code is on STHub [1]. > > Have fun > T. > > [1] http://smalltalkhub.com/#!/~TorstenBergmann/EventRecorder

Jan. 10, 2015

Need a PC users to get the PDF of

by stepharo

Hi guys I would like to add Smalltalk, Objects and Design to our free book libraries and I do not have a pdf saver on mac for google book https://books.google.fr/books?id=W8_Une9cbbgC&printsec=frontcover&num=15&hl… Could one of you give a try? Thanks Stef

Jan. 10, 2015

Re: [Pharo-dev] Prepared Statements on PostgresV2?

by phil＠highoctane.be

This was asked a while ago, here is what I could find: On 29/10/2013 9:32 PM, Yanni Chiu wrote: > > Looking at the postgres docs, I see PREPARE and EXECUTE. I'll try to > start up a postgres server, and see if I can get it to work. > Here's an example: TestPGConnection new executeAll: #( 'PREPARE test1 (text,text,integer,text) AS INSERT INTO films (code, title, did, kind) VALUES ($1,$2,$3,$4)' 'EXECUTE test1(''T_601'', ''Yojimbo'', 106, ''Drama'')' ). TestPGConnection new executeAll: #( 'PREPARE test2 (text) AS SELECT * FROM films WHERE code LIKE $1' 'EXECUTE test2(''%0%'')' ). TestPGConnection new executeAll: #( 'EXECUTE test2(''%1%'')' ). But, the third example returned: ERROR: prepared statement "test2" does not exist So it seems to not remember the prepared statements across connection cycles. Re-sending the prepared statement isn't good for performance, but it does solve your security issue. HTH Phil On Sat, Jan 10, 2015 at 2:06 AM, David Carlos Manuelda <stormbyte(a)gmail.com> wrote: > I've installed and tested PostgresV2 under pharo with the following doits > > Gofer new > smalltalkhubUser: 'PharoExtras' project: 'PostgresV2'; > configuration; > load. > (#ConfigurationOfPostgresV2 asClass project version: '2.4') load > > While it works good, it is missing a very important feature from both > security and performance point of view: The prepared statements. > > As a brief, prepared statements are parameterized SQL statements that are > loaded ONCE per connection instead of sending the whole query to DB every > time, and also, they are parameterized, so it completelly prevents SQL > injection, as the parameters are automatically 'detected' and scaped and/or > handled accordingly without allowing in any case a parameter to alter the > SQL meaning, which can happen by using regular SQL queries made by string > concatenation. > > I browsed the class and did not find any prepare: method nor anything > similar. > > Also, you can give a name to a SQL sentence, which makes the code much more > readable without messing too much logic with SQL commands and string > concatenation. > > An example is as follows: (supposing we have an instance variable > connection, already initialized and connected via PGConnection class) > > Instead of: > > self connection execute: 'SELECT data FROM mytable WHERE name=''', anUser > userName, ''';'. > > Would be something like this: > self connection executePrepared: 'getUserData' with: anUser userName. > > And another suggestion could be something like: > self connection prepare: 'getUserData' withSQL: 'SELECT data FROM mytable > WHERE name=$1' > > Any plan for this to be implemented or any hint to other PostgreSQL class > that already has it? > > David. > > P.S. I've written in my blog about this some time ago: > > http://stormbyte.blogspot.com.es/2012/06/programming-with-database-using.ht… > if someone finds it useful. > > >

Jan. 10, 2015