p...@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
> <stormb...@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.html
>> if someone finds it useful.
>>
>>
>>
