Using php variable inside a query

Question

Hi,

i am using a query inside php as

 $query =  'SELECT * from #__chronoforms_UploadAuthor where text_6 like "%'.$_GET['title'].'%" and text_7 like "%'.$_GET['author'].'%" limit 0,1';

where i am trying to insert a php variable instead of 1 in the limit..

   $query =  'SELECT * from #__chronoforms_UploadAuthor where text_6 like "%'.$_GET['title'].'%" and text_7 like "%'.$_GET['author'].'%" limit 0,"'.$_GET['limit'].'"';

but it shows me the error . Pls help me .. THere are some errors in keeping $_GET['limit']..

Answer 1

Three things:

1. The way you're writing out those queries is a bit hard to read. Personally I prefer using a multi-line heredoc syntax (as per below) but this isn't strictly required;

2. Any user input should go through mysql_real_escape_string() to avoid SQL injection attacks. Note: "user input" includes anything that comes from the client including cookies, form fields (normal or hidden), query strings, etc; and

3. You don't need to quote the second argument to LIMIT clause, which is probably the source of your problem, meaning put LIMIT 0,5 not LIMIT 0,"5".

So try:

$title = mysql_real_escape_string($_GET['title']);
$author = mysql_real_escape_string($_GET['author']);
$limit = (int)$_GET['limit'];

$query = <<<END
SELECT *
FROM #__chronoforms_UploadAuthor
WHERE text_6 LIKE "$title%" 
AND text_7 LIKE "%$author%"
LIMIT 0,$limit
END;

Also, one commentor noted that % and _ should be escaped. That may or may not be true. Many applications allow the user to enter wildcards. If that's the case then you shouldn't escape them. If you must escape them them process them:

$title = like_escape($limit);

function like_escape($str) {
  return preg_replace('!(?|\\)((?:\\)*)([%_])!', '$1\$2', $str);
}

That somewhat complicated regex is trying to stop someone putting in '\%' and getting '\%', which then escape the backslash but not the '%'.

Answer 2

This should work:

$query =  'SELECT * from #__chronoforms_UploadAuthor where text_6 like "%'.$_GET['title'].'%" and text_7 like "%'.$_GET['author'].'%" limit 0,'.$_GET['limit'];

But you really should filter incoming data...

$query =  'SELECT * from #__chronoforms_UploadAuthor where text_6 like "%'.mysql_real_escape_string($_GET['title']).'%" and text_7 like "%'.mysql_real_escape_string($_GET['author']).'%" limit 0,"'.intval($_GET['limit']).'"';

Answer 3

The hash sign (#) starts a comment in SQL, which looks like your problem

Answer 4

You've enclosed the $_GET['limit'] in double-quotes, which is the source of the problem.

Try this:

$query =  'SELECT * from #__chronoforms_UploadAuthor where text_6 like "%'.$_GET['title'].'%" and text_7 like "%'.$_GET['author'].'%" limit 0,'.$_GET['limit'];

Also as Cletus mentions in this answer, there are many, more serious problems you need to resolve.

Answer 5

Remove the double-quotes around $_GET['limit']. The two numbers that the LIMIT clause takes should not be quoted.

Answer 6

Whant bunch of awful answers!

a. to slove limit problem:

$limit=intval($_GET['limit']); 

and then

...LIMIT 0, $limit

in the query.

b. to sanitize $_GET['title'], as many mentioned:

$title = mysql_real_escape_string($_GET['title']);   

so the final code must be

$limit=intval($_GET['limit']); 
$title = mysql_real_escape_string($_GET['title']);   
$author = mysql_real_escape_string($_GET['author']);   
$query = "SELECT * from #__chronoforms_UploadAuthor 
          WHERE text_6 like '$title' and text_7 like '%$author%' 
          LIMIT 0, $limit";