Real Software Forums
http://forums.realsoftware.com/

Constructing SQL Wiith Prepared Statements
http://forums.realsoftware.com/viewtopic.php?f=3&t=47879
Page 1 of 1

Author:  Jason_Adams [ Tue May 14, 2013 12:22 pm ]
Post subject:  Constructing SQL Wiith Prepared Statements

I'm using PostgreSQL, and I recognize the use for things like PostgreSQLPreparedStatements, but I have one issue. My code constructs queries using functions from a SQL module I wrote. That way I can pass things like an array of Int64s and a column name, and receive a "IndexCol=1 OR IndexCol=2...etc.". If I receive a string then I ReplaceAll for single-quotes.

It would be useful to use prepared statements, but they're not helpful in constructing statements from multiple functions. By the time the final query is constructed, the entire Where Clause is a single string; I assume if I passed this to a Prepared Statement it'd throw the whole thing in quotes.

It seems, then, that Prepared Statements are really only useful for basic, non-inherent queries. I feel like I'm wrong, though. Can someone offer an example of how they're constructing SQL while still using prepared statements?

Author:  taylor-design [ Tue May 14, 2013 12:48 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

The where clause would be part of the SQL from which you would build the prepared statement. When you construct it, instead of attaching data, you would attach the number of placeholders to match your array of data: "IndexCol=$1 OR IndexCol=$2...etc.". Then you would pass the actual values during the query.

Author:  Thom McGrath [ Tue May 14, 2013 1:03 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

Daniel's answer is 100% correct. Just wanted to point something out about your SQL. If you're doing "IndexCol=$1 OR IndexCol=$2" that would probably be nicer written as "IndexCol IN ($1,$2)". SQL is one of those things you can use your entire life and still learn new things, so I thought I'd bring it up.

Anyway, the solution isn't the most elegant, but it is the right one. And doing a ReplaceAll on single-quotes isn't good enough to truly be secure, there are other ways to trick the engine. Prepared statements are the only way to be 100% sure, or at least shift the blame off your code if an exploit is found. :P

Author:  Jason_Adams [ Tue May 14, 2013 3:40 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

Thom McGrath wrote:
Daniel's answer is 100% correct. Just wanted to point something out about your SQL. If you're doing "IndexCol=$1 OR IndexCol=$2" that would probably be nicer written as "IndexCol IN ($1,$2)". SQL is one of those things you can use your entire life and still learn new things, so I thought I'd bring it up.

Good call on using IN, it didn't even occur to me that I could do that. SQL is definitely full of lessons, you're so right about that. :)

Thom McGrath wrote:
Anyway, the solution isn't the most elegant, but it is the right one. And doing a ReplaceAll on single-quotes isn't good enough to truly be secure, there are other ways to trick the engine. Prepared statements are the only way to be 100% sure, or at least shift the blame off your code if an exploit is found.

True, which is why I was hoping to figure a way to constructively use prepared statements. Also, after running some tests running queries with and without prepared statements (in the shell, not RS), I'm noticing that Prepared statements always take longer. Do you find this to be fairly fixed, or relative to the number of parameters?

It seems like it's a bit of a hit, but is accepted as it's also a boost in security.

Author:  Jason_Adams [ Tue May 14, 2013 4:11 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

I seem to be wrong about the time. It's faster, although there are cases where it's slower to use a prepared statement (see notes). My particular case doesn't see much of a boost (9.5%), but I suspect others would. I wonder if it's faster to use a prepared statement or a transaction for multiple inserts. :?

Now I'm curious: Does RS actually send the prepare statement upon the construction of the object? I'd like to think so, but I figured I'd ask.

Author:  taylor-design [ Tue May 14, 2013 11:09 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

Regarding speed: I haven't exhaustively tested it. But when I did run some informal tests, I didn't find any real speed difference either way.

I think the difference between prepared and straight text is swamped by other things: database design, indexing, query design, the machine the DB is running on, etc.

Author:  Thom McGrath [ Tue May 14, 2013 11:52 pm ]
Post subject:  Re: Constructing SQL Wiith Prepared Statements

Well, you'll lose performance by not reusing statements. For example, in a loop, don't create a new statement each iteration. Create one statement outside the loop and change the parameters each iteration.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/