[development] Programmatically creating SQL queries

[John Fiala]

On Fri, Mar 11, 2011 at 6:33 AM, Andy Fowlston <andy at pedalo.co.uk> wrote:
> Hi all,
>
> Is there a preferred way in Drupal to programmatically create SQL queries? Currently I'm building a query to pull some webform data from selected component fields. I see a few choices:
>
> 1. Build the query with db_query() placeholders, build an argument array, and make the call using call_user_func_array().

Whoa, don't do that!  Well, do most of that.  But db_query will take
an array of arguments - db_query("SELECT blah...", 1, 2, 3) and
db_query("SELECT blah, array(1, 2, 3)) are the same thing.

> 2. Use sprintf() to build up a complete string, and pass that directly to db_query().

Best not to do this with any inputs that are untrusted.

> 3. Use other functions to sanitise the arguments.
>
> Is there any preferred method? Guessing number 2...

The preferred method is #1, with the modification I mentioned.  #2
works if you're dealing with an array of arguments that have no chance
of being unsafe... but it'll look weird and coder will complain at
you. :)

-- 
John Fiala
www.jcfiala.net