Support for Prepared Statements

[trailsandtribulations]

I do not see support for prepared statements.

seems like query( String query, List params ) would be appropriate, as versus query( String query, Map params ) for regular query.

[xxgreg]

Do you mean like this?

c.execute('insert into my_table values (@a, @b, @c);', [
  {"a": 1, "b": 2, "c": 3},
  {"a": 4, "b": 5, "c": 6},
  {"a": 7, "b": 8, "c": 9},
]).then((result) {
 print(result); // prints [1, 1, 1]
});

This would run the insert statement 3 times.

I like this approach. I'm not sure if it's a good idea to reuse the same method or perhaps create new methods like:

Future<List<int>> executeMulti(String sql, List<Map<String,dynamic>);

Stream<?> queryMulti(String sql, List<Map<String,dynamic>);

I'm not sure what the best return type for queryMulti() would be, it's effectively a stream of streams. I could flatten this, and add some kind of sentinel to signal the end of a result set.

The good thing about having separate methods is it makes the feature more discoverable in the api docs (Which don't exist yet :( )

[trailsandtribulations]

I like your idea, but is not a prepared statement

http://www.postgresql.org/docs/current/static/sql-prepare.html

something like this:

db.query( "prepare emp_update( int, varchar ) as update emp set name=$2 where emp_id=$1", {} );

db.query( 'emp_update', [ 15, 'John Doe' ] )

1. prepared statements are significantly faster
2. prepared statements are significantly more secure*

*I think I can prove that quoting/escaping parameters will not guarantee sql injection attacks - or at least others smarter than me have made that claim.

On Thu, Nov 21, 2013 at 11:35 AM, xxgreg [email protected] wrote:

Do you mean like this?

c.execute('insert into my_table values (@a https://github.com/a, @bhttps://github.com/b, @c);', [ {"a": 1, "b": 2, "c": 3}, {"a": 4, "b": 5, "c": 6}, {"a": 7, "b": 8, "c": 9}, ]).then((result) { print(result); // prints [1, 1, 1] });

This would run the insert statement 3 times.

I like this approach. I'm not sure if it's a good idea to reuse the same method or perhaps create new methods like:

Future> executeMulti(String sql, List);

Stream<?> queryMulti(String sql, List);

I'm not sure what the best return type for queryMulti() would be, it's effectively a stream of streams. I could flatten this, and add some kind of sentinel to signal the end of a result set.

The good thing about having separate methods is it makes the feature more discoverable in the api docs (Which don't exist yet :( )

— Reply to this email directly or view it on GitHubhttps://github.com/xxgreg/postgresql/issues/24#issuecomment-28957586 .

[xxgreg]

Prepared statements can be used by these methods if the list size is large enough to warrant the extra overhead (>20?). I could also add an optional forcePreparedStatement argument.

For small lists, prepared statements are significantly slower - as there are more round trips to the server.

From a sql injection perspective, it doesn't matter if the parameter substitution is done on the client or the server, as long as it is done, and done correctly. The advantage of doing it on the client, is it means you don't have to use pay the overhead of using prepared statements even if you're only submitting the query once.

[trailsandtribulations]

1. I am not suggesting that your db.query() create a prepared statement - although some implementation do this.

I'm suggesting that the developer create a set of commonly used dml as prepared statements to be used later.

that's why db.query( 'query_name, [ param1, ...] )`

1. your idea of forcing the creation of a prepared statement I think is a pretty good idea.
2. if you have the client (in this case the web server) substitute the parameters, it is my understanding that it is very difficult at best to programatically guarantee there is no sql injection attack. (this is a whole other discussion - you can email me bangkokmaco AT gmail we could have it off-line.)

On Thu, Nov 21, 2013 at 11:56 AM, xxgreg [email protected] wrote:

Prepared statements can be used by these methods if the list size is large enough to warrant the extra overhead (>20?). I could also add an optional forcePreparedStatement argument.

For small lists, prepared statements are significantly slower - as there are more round trips to the server.

From a sql injection perspective, it doesn't matter if the parameter substitution is done on the client or the server, as long as it is done, and done correctly. The advantage of doing it on the client, is it means you don't have to use pay the overhead of using prepared statements even if you're only submitting the query once.

— Reply to this email directly or view it on GitHubhttps://github.com/xxgreg/postgresql/issues/24#issuecomment-28958218 .

[xxgreg]

Thank your for your comments. I agree that support for prepared statements is a useful feature. When I initially wrote this driver, I have looked at the protocol to see how to implement this.

I won't have time to do this with in the next couple of months. However, I do have time to answer questions if any one else is interested in implementing this. The protocol documentation can be found here: http://www.postgresql.org/docs/8.1/static/protocol-flow.html#AEN60706

[trailsandtribulations]

once I get up to snuff on Dart, will be happy to contribute. have written drivers for pg before. when ready to begin, will contact you.

On Sun, Nov 24, 2013 at 4:15 AM, xxgreg [email protected] wrote:

Thank your for your comments. I agree that support for prepared statements is a useful feature. When I initially wrote this driver, I have looked at the protocol to see how to implement this.

I won't have time to do this with in the next couple of months. However, I do have time to answer questions if any one else is interested in implementing this. The protocol documentation can be found here: http://www.postgresql.org/docs/8.1/static/protocol-flow.html#AEN60706

— Reply to this email directly or view it on GitHubhttps://github.com/xxgreg/postgresql/issues/24#issuecomment-29141740 .

[xxgreg]

Great. Contributions are very welcome ;)

On Sun, Nov 24, 2013 at 3:42 PM, cc young [email protected] wrote:

once I get up to snuff on Dart, will be happy to contribute. have written drivers for pg before. when ready to begin, will contact you.

On Sun, Nov 24, 2013 at 4:15 AM, xxgreg [email protected] wrote:

Thank your for your comments. I agree that support for prepared statements is a useful feature. When I initially wrote this driver, I have looked at the protocol to see how to implement this.

I won't have time to do this with in the next couple of months. However, I do have time to answer questions if any one else is interested in implementing this. The protocol documentation can be found here: http://www.postgresql.org/docs/8.1/static/protocol-flow.html#AEN60706

— Reply to this email directly or view it on GitHub< https://github.com/xxgreg/postgresql/issues/24#issuecomment-29141740> .

— Reply to this email directly or view it on GitHubhttps://github.com/xxgreg/postgresql/issues/24#issuecomment-29147527 .

[trailsandtribulations]

my pleasure. will be start in about a week (unless I'm stupider in Dart than expected) ;)

On Mon, Nov 25, 2013 at 2:43 AM, xxgreg [email protected] wrote:

Great. Contributions are very welcome ;)

On Sun, Nov 24, 2013 at 3:42 PM, cc young [email protected] wrote:

once I get up to snuff on Dart, will be happy to contribute. have written drivers for pg before. when ready to begin, will contact you.

On Sun, Nov 24, 2013 at 4:15 AM, xxgreg [email protected] wrote:

Thank your for your comments. I agree that support for prepared statements is a useful feature. When I initially wrote this driver, I have looked at the protocol to see how to implement this.

I won't have time to do this with in the next couple of months. However, I do have time to answer questions if any one else is interested in implementing this. The protocol documentation can be found here: http://www.postgresql.org/docs/8.1/static/protocol-flow.html#AEN60706

— Reply to this email directly or view it on GitHub< https://github.com/xxgreg/postgresql/issues/24#issuecomment-29141740> .

— Reply to this email directly or view it on GitHub< https://github.com/xxgreg/postgresql/issues/24#issuecomment-29147527> .

— Reply to this email directly or view it on GitHubhttps://github.com/xxgreg/postgresql/issues/24#issuecomment-29163881 .

[xxgreg]

It's pretty easy to get started with. Make sure you've read up on Futures and Streams.