limit in ajax & mysql

Question

I have a problem when I insert $q in the "SELECT * FROM table LIMIT '".$q."'";

HTML code: the html code saved in "ajax.php" file.

    <html>
<head>
<script>
function showUser(str)
{
if (str=="")
  {
  document.getElementById("txtHint").innerHTML="";
  return;
  } 
if (window.XMLHttpRequest)
  {// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {
  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
    }
  }
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
</script>
<meta charset="utf-8">
</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here.</b></div>

</body>
</html>

and PHP code: this php code saved in "getuser.php" in the same directory with html code.

    <?php
$q = intval($_GET['q']);
$m = 2;

$con = mysqli_connect('localhost','root','','net');

mysqli_select_db($con,"members");
$sql="SELECT * FROM members LIMIT '".$q."'";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";


while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['username'] . "</td>";
  echo "<td>" . $row['password'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['family'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysqli_close($con);
?>

in output show this messege:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\php2\admin\test\getuser.php on line 30

but when i insert "SELECT * FROM members LIMIT 2" It work successfully

let's get rid of the obvious first. Try this line instead of what you have: `$sql="SELECT * FROM members LIMIT ".$q;`. — Sebas, Jan 15 '14 at 18:14

score 1 · Accepted Answer · answered Jan 15 '14 at 18:19

1

You must not escape Integer Values in SQL-Queries. I would write:

    $sql = "SELECT * FROM members LIMIT " . (int)$q . ";";

answered Jan 15 '14 at 18:19

andreaslangsays

354
4
7

You should consider registering user. And the code isn't even escaping: it's passing a string parameter to LIMIT instead of an integer. – NobleUplift Jan 15 '14 at 18:22
(int)$q casts the string to integer, there is no need to escape integers. – andreaslangsays Jun 05 '15 at 11:03
I meant that the OP's code was not escaping input, which the OP should be taught first and foremost. Your solution is good. There used to be a bug in PHP where casting (int) 1a2b3c4d would result in 1234 IIRC, but now it only takes the front portion of the integer, resulting in 1, or 0 if it is not a number. – NobleUplift Jun 09 '15 at 16:29
You're right, escaping input before querying sql is the first thing to learn if you dive into programming. External input is always a source of risk. – andreaslangsays Jun 10 '15 at 11:18

score 1 · Answer 2 · answered Jan 15 '14 at 18:30

this code is extremly dangerous you are allowing anyone to inject into your database. you need to clean the variable first, also there is no need to concat the statement if your using simply double quotes. the $x woudl be interpreted correctly.

function clean_data($string) {
        $string = trim($string);
        if (get_magic_quotes_gpc()) {
            $string = stripslashes($string);
        }
        $string = strip_tags($string);
        return html_entity_decode($string, ENT_QUOTES);
}

$x = clean_data($_GET['q');
$q = "SELECT * FROM members LIMIT $x";

limit in ajax & mysql

2 Answers2