0

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 an error. There are some errors in keeping $_GET['limit'].

hakre
  • 193,403
  • 52
  • 435
  • 836
useranon
  • 29,318
  • 31
  • 98
  • 146

6 Answers6

4

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 then process them:

$title = like_escape($limit);

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

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
cletus
  • 616,129
  • 168
  • 910
  • 942
  • not "user input" but any string value you didn't mention sanitization for the limit parameter and you are only one who use heredoc for the queries. – Your Common Sense Mar 08 '10 at 09:35
  • @Col 1. **all** GET/POST parameters are "user input" and 2. I don't understand your point about heredocs. So what if no one else does? It doesn't make me wrong or them right. – cletus Mar 08 '10 at 09:38
  • 1
    not GET/POST parameters , but **any string value** goes to the query. – Your Common Sense Mar 08 '10 at 09:52
  • @stereofrog: LIKE-escaping is somewhat tricky. If you need to support it, see http://stackoverflow.com/questions/2106207/escape-sql-like-value-for-postgres-with-psycopg2 – bobince Mar 08 '10 at 09:56
  • I didn't notice you sanitize $limit in the code. I beg my pardon – Your Common Sense Mar 08 '10 at 10:23
  • @Col clarification added. Happy yet? – cletus Mar 08 '10 at 10:28
  • @stereofrog added wildcard escaping. – cletus Mar 08 '10 at 10:28
  • nope :) escaping intentded not for the "user input", but for the query content. Source of input doesn't matter. It can be user or server, form or file. it is not "sanitization" but merely syntax rule. 2-step rule: enclose string contents into quotes and escape special characters in it. As a matter of fact, it has no relation to the user input. – Your Common Sense Mar 08 '10 at 10:59
  • use prepared statements instead of `mysql_real_escape_string` if you do not know 100 % what you are doing (which seems to be the case for the op). `mysql_real_escapey_string` still can cause problems, when used for uncasted number-values – knittl Mar 09 '10 at 09:27
1

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

1

Want bunch of awful answers!

a. To solve the 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";
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

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.

codaddict
  • 445,704
  • 82
  • 492
  • 529
0

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

Kai Chan
  • 2,463
  • 1
  • 14
  • 13
-1

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']).'"';
Christoffer
  • 25,035
  • 18
  • 53
  • 77
  • 1
    addslashes is not a suffient method to escape mysql injections, using mysql_real_escape_string will help in that topic. – Cem Kalyoncu Mar 08 '10 at 08:53
  • `'limit 0,"'.addslashes($_GET['limit']).'"'` still doesn't work either, since the limit can't be a string. It should be `limit 0,'.intval($_GET['limit']).'` – Zarel Mar 08 '10 at 08:57