PHP script not returning intended JSON response

Question

I need some help.. I have a php script which performs a search on a database based on 3 keywords, and the queries work great on the phpadmin for the database but the returned JSON response finds no result ( "No empresa found" ). can anyone tell me why? If I perform the single table search, It works perfectly. ( In case I just use on keyword associated with one table )

Keyword 1 is for Name on Table A Keyword 2 is for PriceRange on table Prices Keyword 3 is for Sector on Table Sectors

Tables are like:

Table A                         Table Prices               Table Sectors
Name   PriceRange   Sector       ID      Pricerange           ID      Sector
XY          1         2          1         100€ to 200€        1         Computers
YX          2         1          2         200€ to 300€        2         Tablets

PHP script

<?php

error_reporting(0);

require_once('db_config.php');

$conn = mysql_connect($db_server, $db_user, $db_password) or die(mysql_error());

$db=  mysql_select_db($db_database, $conn);

header('content-type: application/json; charset=utf-8');
mysql_query('SET character_set_connection=utf8');
mysql_query('SET character_set_client=utf8');
mysql_query('SET character_set_results=utf8');

// array for JSON response
$response = array();

// get empresa based on keyword
$keyword=$_GET["keyword"];
$keyword2=$_GET["keyword2"];
$keyword3=$_GET["keyword3"];

if($keyword2 == "Todos os Setores" and $keyword3 == "Qualquer Investimento" ): 
    $result = mysql_query("SELECT * FROM empresa WHERE marca LIKE '%$keyword%' and ficha LIKE '%1' ") or die(mysql_error());
elseif($keyword2 == "Todos os Setores"): 
    $result = mysql_query("SELECT empresa.ficha, empresa.marca, empresa.empresa, empresa.descricao, empresa.imagempequena from empresa , investimento where investimento.investimento='%$keyword3%' and empresa.nivelinvestimento=investimento.id and empresa.ficha LIKE '%1' and empresa.marca LIKE '%$keyword%'") or die(mysql_error());
elseif($keyword3 == "Qualquer Investimento"):
    $result = mysql_query("SELECT empresa.ficha, empresa.marca, empresa.empresa, empresa.descricao, empresa.imagempequena from empresa, sector where sector.sector='%$keyword2%' and empresa.sector=sector.id and empresa.ficha LIKE '%1' and empresa.marca LIKE '%$keyword%'") or die(mysql_error());
else:
    //query a funcionar
    $result = mysql_query("SELECT empresa.ficha, empresa.marca, empresa.empresa, empresa.descricao, empresa.imagempequena from empresa , investimento, sector where investimento.investimento='%$keyword3%' and empresa.nivelinvestimento=investimento.id and sector.sector='%$keyword2%' and empresa.sector=sector.id and empresa.ficha LIKE '%1' and empresa.marca LIKE '%$keyword%'") or die(mysql_error());
endif;

// check for empty results
if (mysql_num_rows($result) > 0) {
    // looping through all results
    $response["empresa"] = array();

     while ($row = mysql_fetch_array($result)) {
        // temp user array
        $empresa= array();
        $empresa["marca"] = $row["marca"];
        $empresa["empresa"] = $row["empresa"]; 
        $empresa["descricao"] = $row["descricao"];
        $empresa["imagempequena"] = $row["imagempequena"];

        // push single empresa array into final response array

        array_push($response["empresa"], $empresa);
    }
    // success
    $response["success"] = 1;

    // echoing JSON response
    echo json_encode($response);
} else {
    // no products found
    $response["success"] = 0;
    $response["message"] = "No empresa found";

    // echo no users JSON
    echo json_encode($response);
}
?>

**warning** your code is extremely vulnerable to sql injection attacks! — Daniel A. White, Oct 29 '14 at 14:25
I get that a lot. I would like to understand how this works first and then deal with that, somehow.. — Acejakk, Oct 29 '14 at 14:27
http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Daniel A. White, Oct 29 '14 at 14:28
what are the values for the 3 keywords? Can you echo the query that is ran? — cornelb, Oct 29 '14 at 14:28
done any basic debugging, like checking if your `if()` tree ever actually matches anything? — Marc B, Oct 29 '14 at 14:31
edited to add keyword values. Thanks Daniel, I will definitly check it out — Acejakk, Oct 29 '14 at 14:32
Why don't you let your script print out all the errors for debugging? If everything is working then turn off error_reporting. — beeef, Oct 29 '14 at 14:32
@MarcB Yes, on phpmyadmin all queries actually work. they keywords I recieve from the android app are getting there. to return 0 means the number of rows is 0 which shouldn't be true — Acejakk, Oct 29 '14 at 14:33
yes, but that's phpmyadmin. you should add debug output to your script to see WHICH of those queries actually executed, and see what the sql really was. e.g. `$sql = "SELECT ..."; var_dump($sql);`. — Marc B, Oct 29 '14 at 14:34
@beeef unexpected character at line 1 column 1 . I used it so I always got a valid JSON response. — Acejakk, Oct 29 '14 at 14:36
@MarcB If I only input keyword1 which searched table A, it works. Now if I try to add more parameters to the search ( from other tables ) it just returns 0. meaning whenever I try to use keyword 2 and 3 from other tables AND keyword 1 it returns 0 — Acejakk, Oct 29 '14 at 14:38
as @MarcB already said, put your SQL-queries in an own variable, and echo the complete SQL statement. Maybe you have something you don't want.. — beeef, Oct 29 '14 at 14:39

score 1 · Accepted Answer · answered Oct 29 '14 at 14:50

1

Here's your problem:

where investimento.investimento='%$keyword3%'
                               ^---

In most of your queries, you're using SQL wildcards, which ONLY work when using the LIKE operator. When doing direct equality tests =, the wildcards are plain characters with no special meaning

That means, if you're doing a search for a keyword foo, you're telling the DB to do an exact string match on records containing the literal characters %, f, o, o, and %.

e.g. these two queries are directly equivalent:

WHERE foo = '%bar%'
WHERE substr(foo, 0, 1) = '%' AND substr(foo, 1, 1) = 'b' AND ....

and these two as well

WHERE foo LIKE '%bar%'
WHERE LOCATE('bar', foo) <> 0

And note that, as others have pointed out, you're vulnerable to sql injection attacks.

answered Oct 29 '14 at 14:50

Marc B

356,200
43
426
500

I will try it out now. I did change all my keyword to the above statement to prevent sql injection – Acejakk Oct 29 '14 at 14:52
This is absolutely correct. As I said, using '%".mysql_real_escape_string($keyword)."%' instead of '%keyword%' solves my sql injection problems? – Acejakk Oct 29 '14 at 15:00
no. the proper solution is to ditch the mysql_*() functions since they're obsolete/deprecated, and switch over to using PDO with prepared statements and placeholders. – Marc B Oct 29 '14 at 15:01
So no need to use that instead of just '%keyword%' right? I will switch to PDO then.. – Acejakk Oct 29 '14 at 15:08
you need to use `like`, not `=`. That solves your immediate problem. the sql injection and pdo stuff is a meta problem. – Marc B Oct 29 '14 at 15:08
It is already working, I used like. I was just asking about the security issue. I will search PDO prepared statements and placeholders to solve security issues. Thank you for all the help! – Acejakk Oct 29 '14 at 15:14

score 0 · Answer 2 · answered Oct 29 '14 at 14:38

0

The problem with your codes is with the quotes '%keyword%' try this instead '%".mysql_real_escape_string($keyword)."%' and honestly you are pron to sql injections so that piece of code should take care of most of your worries. ;)

answered Oct 29 '14 at 14:38

Alex Davies

49
9

I will try it out asap. that also covers most of my sql injection problems? – Acejakk Oct 29 '14 at 14:41
yes! for basic security. Don't forget to vote up if it solves your issue. – Alex Davies Oct 29 '14 at 14:46
I did apply it for all my keywords and the first query still works. However, the problem with the other three queries remain ( In case I use keyword1 and 2, keyword1 and 3, or keyword 1,2 and 3 ) . The multi table queries are the only ones not working – Acejakk Oct 29 '14 at 14:50

PHP script not returning intended JSON response

2 Answers2