New topic: Constructing SQL Wiith Prepared Statements
<http://forums.realsoftware.com/viewtopic.php?t=47879> Page 1 of 1 [ 7 posts ] Previous topic | Next topic Author Message Jason_Adams Post subject: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 12:22 pm Joined: Fri Nov 10, 2006 4:10 pm Posts: 1834 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 taylor-design Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 12:48 pm Joined: Wed Mar 22, 2006 11:15 am Posts: 686 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 Thom McGrath Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 1:03 pm Site Admin Joined: Tue May 06, 2008 1:07 pm Posts: 1458 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. _________________ Thom McGrath - @tekcor Web Framework Architect, Real Software, Inc. Top Jason_Adams Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 3:40 pm Joined: Fri Nov 10, 2006 4:10 pm Posts: 1834 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 Jason_Adams Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 4:11 pm Joined: Fri Nov 10, 2006 4:10 pm Posts: 1834 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 taylor-design Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 11:09 pm Joined: Wed Mar 22, 2006 11:15 am Posts: 686 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 Thom McGrath Post subject: Re: Constructing SQL Wiith Prepared StatementsPosted: Tue May 14, 2013 11:52 pm Site Admin Joined: Tue May 06, 2008 1:07 pm Posts: 1458 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 Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 7 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
