Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Mon Dec 18, 2017 5:50 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 12:22 pm 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
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?

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 12:48 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
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.

_________________
Daniel L. Taylor
Custom Controls for Real Studio WE!
Visit: http://www.webcustomcontrols.com/


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 1:03 pm 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
Location: NotEvenOnTheMap, CT
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

_________________
Thom McGrath - @tekcor
Web Framework Architect, Real Software, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 3:40 pm 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
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.

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 4:11 pm 
Offline
User avatar

Joined: Fri Nov 10, 2006 4:10 pm
Posts: 1855
Location: Michigan, USA
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.

_________________
Windows 8 x64
Windows XP Pro SP3
Ubuntu 11.04 via Virtual Box
RS Enterprise 2012r1.1

Programming Tutorials & Free Projects: http://www.JasonTheAdams.com
"Christianity has not been tried and found wanting; it has been found difficult and not tried." - G.K. Chesterton


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 11:09 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
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.

_________________
Daniel L. Taylor
Custom Controls for Real Studio WE!
Visit: http://www.webcustomcontrols.com/


Top
 Profile  
Reply with quote  
 Post subject: Re: Constructing SQL Wiith Prepared Statements
PostPosted: Tue May 14, 2013 11:52 pm 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
Location: NotEvenOnTheMap, CT
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.

_________________
Thom McGrath - @tekcor
Web Framework Architect, Real Software, Inc.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group