Trolltech Home | Qt-interest Home | Recent Threads | All Threads | Author | Date
All threads index page 7

Qt-interest Archive, May 2008
Binding parameter lists in SQL queries

Message 1 in thread

Hi,

I'm trying to write a query with this basic syntax:

SELECT foo.name FROM foo WHERE foo.id IN ( ?, ?, ?, ? );
or
SELECT foo.name FROM foo WHERE foo.id IN ( ? );
or
SELECT foo.name FROM foo WHERE foo.id IN ( ?, ?, ?, );

etc.

where the list of parameters might be varying in length (n > 0) and
changes at runtime.

The best solution I have at the moment is to write one function to
generate the correct number of question marks in a query string, and
then another function that loops over a list of values and binds them
to the query.

Is there a better way to do this in Qt?

Many thanks.

Chris

--
 [ signature omitted ]
Message 2 in thread

Store the parameters in a QStringList and build the query up each time.

QStringList params;
params += "blah";
params += "potato";
params += "dingleberry";
params += "okigetthepointnow";

QString querystr = "SELECT foo.name FROM foo WHERE foo.id IN ( ";
querystr += params.join( QString(",") );
querystr += " );" ;


Thatâs just off the cuff - untested. 
No points for style either, the main point of the example is 'join'. 

If the parameters come from the user, make sure they are sanitized ( no ; or DROPs etc :) )

Does this help?

Cheers
Marc




-----Original Message-----
From: Chris Mueller [mailto:asciilife@xxxxxxxxx] 
Sent: Saturday, 31 May 2008 11:12 AM
To: qt-interest@xxxxxxxxxxxxx
Subject: Binding parameter lists in SQL queries

Hi,

I'm trying to write a query with this basic syntax:

SELECT foo.name FROM foo WHERE foo.id IN ( ?, ?, ?, ? );
or
SELECT foo.name FROM foo WHERE foo.id IN ( ? );
or
SELECT foo.name FROM foo WHERE foo.id IN ( ?, ?, ?, );

etc.

where the list of parameters might be varying in length (n > 0) and
changes at runtime.

The best solution I have at the moment is to write one function to
generate the correct number of question marks in a query string, and
then another function that loops over a list of values and binds them
to the query.

Is there a better way to do this in Qt?

Many thanks.

Chris

--
 [ signature omitted ] 
Message 3 in thread































I'd rather dynamically build a query with placeholders, and then use parameter bindings to pass values to the query.
The benefits of this is that there is a greater chance that the database will be able to find the query plan in the cache, leading to higher throughput.

This will also support a wider range of datatypes without having to resort to conditional formatting of each parameter.
Using placeholders also reduce the risk of SQL injection.

If the parameters come from the database, rather than from the application or the user, I'd prefer to use a sub-query or a join, as that would let the database take care of all the dirty work, and also make your code easier.

Cheers
--
 [ signature omitted ]