where clause not displaying data

Question

hi

i am trying to display data based on wether data in a field is new. instead of showing only the data that is new it is showing all data. can someone point out my error. many thanks

<?php
include("../../js/JSON.php");
$json = new Services_JSON();
// Connect to MySQL database
mysql_connect('localhost', 'root', '');
mysql_select_db(sample);
$page = 1; // The current page
$sortname = 'id'; // Sort column
$sortorder = 'asc'; // Sort order
$qtype = ''; // Search column
$query = ''; // Search string
$new = 1;
// Get posted data
if (isset($_POST['page'])) {
$page = mysql_real_escape_string($_POST['page']);
}
if (isset($_POST['sortname'])) {
$sortname = mysql_real_escape_string($_POST['sortname']);
}
if (isset($_POST['sortorder'])) {
$sortorder = mysql_real_escape_string($_POST['sortorder']);
}
if (isset($_POST['qtype'])) {
$qtype = mysql_real_escape_string($_POST['qtype']);
}
if (isset($_POST['query'])) {
$query = mysql_real_escape_string($_POST['query']);
}
if (isset($_POST['rp'])) {
$rp = mysql_real_escape_string($_POST['rp']);
}
// Setup sort and search SQL using posted data
$sortSql = "order by $sortname $sortorder";
$searchSql = ($qtype != '' && $query != '') ? "where ".$qtype." LIKE '%".$query."%' AND new = 1" : '';
// Get total count of records
$sql = "select count(*)
from act
$searchSql";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$total = $row[0];
// Setup paging SQL
$pageStart = ($page -1)*$rp;
$limitSql = "limit $pageStart, $rp";
// Return JSON data
$data = array();
$data['page'] = $page;
$data['total'] = $total;
$data['rows'] = array();
$sql = "select *
from act
$searchSql 
$sortSql
$limitSql";
$results = mysql_query($sql);
while ($row = mysql_fetch_assoc($results)) {
$data['rows'][] = array(
'id' => $row['id'],
'cell' => array($row['id'], $row['slot'], $row['service'], $row['activity'], $row['department'], $row['company'], $row['address'], $row['user'], $row['item'], $row['filebox'], date('d/m/Y',strtotime($row['date'])), $row['quantity'], $row['type'], $row['new'])
);
}
echo $json->encode($data);
?>

Answer 1

• You should debug SQL by looking at the SQL query, not at the PHP code that produces the SQL query. If you echo $sql and look at it, you'll probably see any syntax errors much more easily.

  You can also copy & paste that SQL and try to execute it in the MySQL command tool, and see what happens, whether it gives the result you want, you can profile it or use EXPLAIN, etc.

• You're using mysql_real_escape_string() for integers, column names, and SQL keywords (ASC, DESC). That escape function is for escaping only string literals or date literals. It's useless for escaping unquoted integers, column names, SQL keywords, or any other SQL syntax.

  For integers, use (int) to typecast inputs to an integer.

  For column names or SQL keywords, use a whitelist map -- see example in my presentation http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies

• You're not testing for error statuses returned by any of your functions. Most functions in ext/mysql return false if some error occurs. You should check for that after every call to a mysql function, and report errors if they occur.

• You're selecting a database using a constant name sample instead of a quoted string "sample". This might be intentional on your part, I'm just noting it.

• Also, this is not related to your errors, but you should really upgrade to PHP 5. PHP 4 has been end-of-lifed for over two years now.

Answer 2

after looking at the code again and all the suggestions i think i should be using an AND clause and not WHERE. for example the code $searchSql = ($qtype != '' && $query != '') ? "where ".$qtype." LIKE '%".$query."%' AND new = 1" : ''; this is the WHERE clause? which basically translates to:

$sql = "select * from act $searchSql $sortSql $limitSql"; <- original code

$sql = "select * from act WHERE company LIKE '%demo%' AND new = 1 $sortSql $limitSql";<-updated code

am i on the right track?