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
– Daniel A. White, Commented Oct 29, 2014 at 14:25
I get that a lot. I would like to understand how this works first and then deal with that, somehow.. — Acejakk
– Acejakk, Commented Oct 29, 2014 at 14:27
Why don't you let your script print out all the errors for debugging? If everything is working then turn off error_reporting. — beeef
– beeef, Commented Oct 29, 2014 at 14:32
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
– Marc B, Commented Oct 29, 2014 at 14:34

Marc B · Accepted Answer · 2014-10-29 14:50:20Z

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, 2014 at 14:50

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Acejakk Over a year ago

I will try it out now. I did change all my keyword to the above statement to prevent sql injection

Acejakk Over a year ago

This is absolutely correct. As I said, using '%".mysql_real_escape_string($keyword)."%' instead of '%keyword%' solves my sql injection problems?

Marc B Over a year ago

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.

Acejakk Over a year ago

So no need to use that instead of just '%keyword%' right? I will switch to PDO then..

Marc B Over a year ago

you need to use like, not =. That solves your immediate problem. the sql injection and pdo stuff is a meta problem.

|

Alex Davies · Accepted Answer · 2014-10-29 14:38:46Z

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, 2014 at 14:38

Alex Davies

499 bronze badges

3 Comments

Acejakk Over a year ago

I will try it out asap. that also covers most of my sql injection problems?

Alex Davies Over a year ago

yes! for basic security. Don't forget to vote up if it solves your issue.

Acejakk Over a year ago

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

Collectives™ on Stack Overflow

PHP script not returning intended JSON response

2 Answers 2

6 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related