Search & replace

[Ike]

I'm absolutely stuck, hoping someone can illuminate my sitatuation. I have a
string (a query) where I need to dynamically be able to change one part of
the the string (the part where .username LIKE\'%\' ), changing the text I am
looking for .username to be LIKE, to something else that will sent over via
an HTTP Get as $_GET['username']. THat is, the quey can be ANY query,
however, if there is a .username LIKE in the query, I need to change the
value for the LIKE (which is % in this case) to be $_GET['username'].

Can someone please help me out with this? A typical query would be something
like:


$qid = mysql_query(str ipslashes("SELE CT t0.username AS \"Username\"
,COUNT(t1.close d) AS \"Ups Handled\" , AVG(ABS(t1.clos ed)) AS \"Closing
Ratio\" FROM associates t0,leads t1 WHERE (t0.branch LIKE \'%\') AND
(t1.associateke y=t0.id) AND t0.username LIKE\'%\' AND t1.date

>=\'2006-01-01%\' AND t1.date <=\'2006-12-31%\' GROUP BY

t0.username ORDER BY t0.username ASC "));

thanks, Ike

[Jerry Stuckle]

Re: Search &amp; replace

Ike wrote:

I'm absolutely stuck, hoping someone can illuminate my sitatuation. I have a
string (a query) where I need to dynamically be able to change one part of
the the string (the part where .username LIKE\'%\' ), changing the text I am
looking for .username to be LIKE, to something else that will sent over via
an HTTP Get as $_GET['username']. THat is, the quey can be ANY query,
however, if there is a .username LIKE in the query, I need to change the
value for the LIKE (which is % in this case) to be $_GET['username'].
>
Can someone please help me out with this? A typical query would be something
like:
>
>
$qid = mysql_query(str ipslashes("SELE CT t0.username AS \"Username\"
,COUNT(t1.close d) AS \"Ups Handled\" , AVG(ABS(t1.clos ed)) AS \"Closing
Ratio\" FROM associates t0,leads t1 WHERE (t0.branch LIKE \'%\') AND
(t1.associateke y=t0.id) AND t0.username LIKE\'%\' AND t1.date

>=\'2006-01-01%\' AND t1.date <=\'2006-12-31%\' GROUP BY

t0.username ORDER BY t0.username ASC "));
>
thanks, Ike
>
>

First of all, you should use mysql_real_esca pe_string() instead on any
data you use. You should also validate the username field before
sending it - since it's a GET parameter, anyone could put almost
anything in there (also true for POST, but only a tiny bit harder).
Then you can just use the username when building your SQL.

Also, you have several other problems in your SQL.

It's probably not a good idea to have a space in the aliases. And
strings are surrounded by single quotes, not double quotes in SQL.

Also, "t0.usernam e like '%'" is meaningless - it will match any non-null
value.

And "t1.date >= '2006-01-01%' won't work. If you're going to use '%'
you must use like. If you're looking for anything >= 1/1/2006, just
compare like that.

Something like this (not checked):

$username = isset($_GET['username']) ? $_GET['username'] : null;

if ($username ... // validation here
$qid = mysql_query("SE LECT t0.username AS Username,
COUNT(t1.closed ) AS Ups_Handled\" ,
AVG(ABS(t1.clos ed)) AS Closing_Ratio
FROM associates t0,leads t1
WHERE t0.branch LIKE '".mysql_real_e scape_string($u sername)."%' AND
t1.associatekey =t0.id AND
t1.date >= '2006-01-01' AND
t1.date <= '2006-12-31'
GROUP BY t0.username
ORDER BY t0.username ASC");

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===