phil@highoctane.be wrote:

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 Thanks for the info, yes it can be done via execute manually, just that I was suggesting to have proper code for handling it.

Furthermore, as per the way prepared statements are designed, they are not saved across database connections, so on every new connection you have to send them, or at least the ones you will be using (calling).

On Sat, Jan 10, 2015 at 2:06 AM, David Carlos Manuelda <stormbyte@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.htm... if someone finds it useful.

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@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.htm... if someone finds it useful.

Le 10 janv. 2015 20:04, "Levente Uzonyi" <leves@elte.hu> a écrit :

The V2 version of the protocol doesn't support prepared statements.

When we were writing the PostgresV3 package, we planned to implement

prepared statements (via the extended query protocol), but later we decided to not do it.

Why?

The protocol is way more complex than the simple query protocol (which is

used for regular queries), and involves more message sends, which can increase the network latency.

The security that prepared statements give is equivalent to proper

escaping. That can and should be implemented for standard queries too.

The performance "improvement" what prepared statements give has two

sides. You save the cost of parsing and planning, but the query is executed with the same plan, no matter what the arguments are. This can lead to sub-optimal performance.

With the extended query protocol you can execute one statement per query.

With the simple query protocol you can execute as many as you want. This can increase the network latency significantly for prepared statements.

In PostgreSQL, calling functions using the simple query protocol behave

similarly to prepared statements. You don't have to pass your SQL query to the server all the time, just the ame of the function and its arguments wrapped in a very simple SQL query.

This way the plan is created when the function is executed the first time (so it's even superior to prepared statements, because there's no per-connection parsing and planning cost).

Functions also let you to use any procedural language PostgreSQL supports, which are currently SQL, PL/pgSQL, Pl/Tcl, PL/Perl, PL/Python. Using the high level languages makes it easier to write complex queries, or express complex logic, which wouldn't be possible with a single SQL query.

So we decided to use functions instead. We added some image side tools to make it easier to write, save, load and debug functions. Currently only the PL/pgSQL language is supported by these tools, and there are some other limitations, but we'll implement new features as time permits.

What about performance?

Using our toolchain, a single process can execute 2500-3000 queries (using a modern CPU, and CogVM) per second, and an image can make about 5-6k queries per second overall. This includes the overhead of using the connection pool, and the generation of the textual SQL query for each function call. Without these it's possible to go above 10k/second.

About connection pool: how does one uses it? Is there a doc somewhere ? Phil

Text or binary?

We implemented the text-based protocol, but added the hooks for the binary protocol too. Why? The binary protocol is undocumented (its documentation is the C source code), and it's subject to change with each release. The text-based protocol is a bit better documented, and it should work with all versions of PostgreSQL starting from 7.4.

Levente

On Sat, 10 Jan 2015, David Carlos Manuelda 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.htm... if someone finds it useful.

...

Levente Uzonyi wrote:

The V2 version of the protocol doesn't support prepared statements.

When we were writing the PostgresV3 package, we planned to implement prepared statements (via the extended query protocol), but later we decided to not do it.

Why?

The protocol is way more complex than the simple query protocol (which is used for regular queries), and involves more message sends, which can increase the network latency.

The security that prepared statements give is equivalent to proper escaping. That can and should be implemented for standard queries too.

The performance "improvement" what prepared statements give has two sides. You save the cost of parsing and planning, but the query is executed with the same plan, no matter what the arguments are. This can lead to sub-optimal performance.

With the extended query protocol you can execute one statement per query. With the simple query protocol you can execute as many as you want. This can increase the network latency significantly for prepared statements.

In PostgreSQL, calling functions using the simple query protocol behave similarly to prepared statements. You don't have to pass your SQL query to the server all the time, just the ame of the function and its arguments wrapped in a very simple SQL query. This way the plan is created when the function is executed the first time (so it's even superior to prepared statements, because there's no per-connection parsing and planning cost).

Functions also let you to use any procedural language PostgreSQL supports, which are currently SQL, PL/pgSQL, Pl/Tcl, PL/Perl, PL/Python. Using the high level languages makes it easier to write complex queries, or express complex logic, which wouldn't be possible with a single SQL query.

So we decided to use functions instead. We added some image side tools to make it easier to write, save, load and debug functions. Currently only the PL/pgSQL language is supported by these tools, and there are some other limitations, but we'll implement new features as time permits.

What about performance?

Using our toolchain, a single process can execute 2500-3000 queries (using a modern CPU, and CogVM) per second, and an image can make about 5-6k queries per second overall. This includes the overhead of using the connection pool, and the generation of the textual SQL query for each function call. Without these it's possible to go above 10k/second.

Text or binary?

We implemented the text-based protocol, but added the hooks for the binary protocol too. Why? The binary protocol is undocumented (its documentation is the C source code), and it's subject to change with each release. The text-based protocol is a bit better documented, and it should work with all versions of PostgreSQL starting from 7.4.

Levente

Well, I want to point a thing to be considered regarding prepared statements. It is not only about security/performance, using them also helps to have your code cleaner and read it better. * No need to use ' character for text elements inside SQL query Avoid the mess that: execute: 'INSERT INTO table VALUES (''', aUser id, ''', ''', Auser otherField, ''');' Confusing and hard to read ' Instead, with prepared is something like 'INSERT INTO table VALUES ($1,$2)' * No need to filter data ( even more readable code ) * Ability to name the SQL so you can use it later by name ( for comodity ) Just take this in consideration if it is going to be evaluated for implementation :)

On Sat, 10 Jan 2015, David Carlos Manuelda 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.htm... if someone finds it useful.