[dancer-users] Complex Query ?

[David Precious]

On Tue, 28 Jan 2014 09:34:02 +0100
Hugues <hugues at max4mail.com> wrote:

> Hello
> I use dancer 1 with with mysql
> 
> @resultCC  = database('db')->quick_select('ClientContrat',
> { NumContrat => { like => "$search%" } }, { limit => 10 } );
> 
> I send @resultCC to my template tt and it's work well
> 
> I would like to pass complex query
<snipped SQL> 
> I do not find solution to use current mysql connection , and 
> quick_select ?  ( it 's ok only if I create new connection with , 
> connect, prepare execute ....)
> what is the best way to do that ?

The database() keyword provided by Dancer::Plugin::Database gives you a
DBI connection handle (well, a subclass of one), so you can use it just
as you'd use DBI for more complex stuff, e.g.

my $sth = database('db')->prepare($sql);
$sth->execute;

template 'foo', { clients => $sth->fetchall_arrayref }

Also, in your example you had:

> ... WHERE Client.CodeClient LIKE '%" .$search. "%' LIMIT 10";

Customary Bobby Tables warning - if you're going to interpolate a
variable directly, use e.g. database->quote($search) to make sure it's
quoted appropriately.  Otherwise, what if $search contained e.g. "foo%';
TRUNCATE TABLE Client; --"?  Don't leave yourself open to SQL
injection, it's a very common mistake.


-- 
David Precious ("bigpresh") <davidp at preshweb.co.uk>
http://www.preshweb.co.uk/     www.preshweb.co.uk/twitter
www.preshweb.co.uk/linkedin    www.preshweb.co.uk/facebook
www.preshweb.co.uk/cpan        www.preshweb.co.uk/github