Re: Using q() to define a query

I beleive most SQL parsers will ignore white space like that in an SQL query. Normally single quotes in parameters are the bug bear of the the SQL programmer,

ie

select name,rank,ser_no from sailors where name = O'Tool

It is alway good practice to use parameterized queries instead that you prepared first

my $sql="select name,rank,ser_no from sailors where name =:name";
my $c=$db->prepare($sql);
$c->bind_param(":name","O'Tool");
$c->execute();

or
my $sql="select name,rank,ser_no from sailors where name =?";
my $c=$db->prepare($sql);
$c->execute("O'Tool");


as DBI and DBD driver will take care of the quotes for you and it prevents any SQL injection attacks on your app.

Cheers

Colin Wetherbee wrote:
Greetings.

I have a DBI (DBD::Pg) application I'm building in mod_perl. My queries tend to look something like the following.

my $sql = q(SELECT departure_date, eq.name AS equipment,
dp.full_city AS departure_city, ap.full_city AS arrival_city,
ca.name AS carrier_name, number
FROM jsjourneys
FULL OUTER JOIN jscarriers AS ca ON jsjourneys.carrier = ca.id
FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id
JOIN jsports AS dp ON jsjourneys.departure_port = dp.id
JOIN jsports AS ap ON jsjourneys.arrival_port = ap.id
ORDER BY departure_date);

And, then, I execute them as follows.

$dbh->selectall_arrayref($sql, { Slice => {} });

Which works quite well.

However, I'm concerned about $sql because when I output it to Apache's debug log, it looks like this:

[Fri Jan 11 03:49:09 2008] [debug] Log.pm(36): [client 192.168.171.80] [JetSet] SELECT departure_date, eq.name AS equipment,\n dp.full_city AS departure_city, ap.full_city AS arrival_city,\n ca.name AS carrier_name, number\n FROM jsjourneys\n FULL OUTER JOIN jscarriers AS ca ON jsjourneys.carrier = ca.id\n FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id\n JOIN jsports AS dp ON jsjourneys.departure_port = dp.id\n JOIN jsports AS ap ON jsjourneys.arrival_port = ap.id\n ORDER BY departure_date

Notice the newline characters in there. If those were really in the query, I can't imagine the database would run it, so I suppose they're an artifact of the combination of using q() to quote my query and using Apache's logger to output it.

All this leads up to a pretty simple question: is using q() to quote my queries a bad thing, and/or will it cause trouble in the future?

(As an aside, how do you guys quote your queries? I find that for anything longer than about 60 characters, q() and '' and everything else start to look horribly inelegant.)

Thanks.

Colin
.

Relevant Pages

  • Re: Help please on Record sets
    ... I tried running it from the query builder after deleting all the ... Just because your "original SQL string was generated in a working ... I had to add a quote to your first Formated Date and Change the quotes to ... recordset or would that lead to more problems? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query with left outer join all of a sudden wont work?
    ... just doesn't work if it is a left or right outer join. ... rather than on the top most query calling it). ... SELECT * FROM [qryFarmer Contracts] ... can you post the whole SQL statement? ...
    (microsoft.public.access.queries)
  • Re: Need help to modify my query
    ... This is not very well-suited to SQL, ... Here's a query that will produce this report - it works ... then uses an outer join, since there may be different numbers ... left outer join S_usergroup as sug ...
    (microsoft.public.sqlserver.programming)
  • again and again... query analyzer and wrong SQL statement plan... + outer join?
    ... again we have some SQL statement performance problems... ... we have aproblem with a query which use 1 inner join and 3 left outer ... Each outer join is a sub-query which contain a group by: ... This query takes more then 10 minutes, but if I precalculate each sub-query ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Cartesian product & outer join
    ... An Ambiguous Outer Join basically means that your arrows are pointing the ... My suggestion to most query problems is to not try to do it in just one ... >> Post your SQL. ... >>> time with overtime time categories and multiplies by civilian overtime ...
    (microsoft.public.access.queries)