Unable to pass 'is not null' query to server side

Question

Situation

I want to search data from database.

My logic

If my textfield length is zero then pass 'is not null' value to server side. Otherwise pass the textfield data to server side.

Client side:

 UILabel *first=[[UILabel alloc]init];
 UILabel *sec=[[UILabel alloc]init];

 if (_zip.text.length==0) {
    first.text=@"is not null";
 }
 else
 if (_zip.text.length>0) {
 first.text=_zip.text;
 }
 if (_par.text.length==0) {
 sec.text=@"is not null";
 }
 else if (_par.text.length>0){
 sec.text=_par.text;
 }


 NSString *selquery=[NSString   stringWithFormat:@"http://localhost/filtering1.php?zipcode=%@&parking=%@",first.text,sec.text];


 NSData *data=[NSData dataWithContentsOfURL:[NSURL URLWithString:selquery]];
 NSString *str=[[NSString alloc]initWithData:data encoding:NSUTF8StringEncoding];
 NSLog(@"%@",str);

Server Side

    <?php

    $host="localhost";
    $username="root";
    $password="";
    $db_name='BuyAndSell';
    mysql_connect("$host", "$username", "$password")or die("cannot connect");
    mysql_select_db("$db_name")or die("cannot select DB");


    $zipcode=$_GET['zipcode'];
    $parking=$_GET['parking'];

    $sql="SELECT * FROM bas WHERE zipcode='$zipcode' and parking='$parking'";

   if ($result = mysql_query($sql)){

   $resultArray = array();
   $tempArray = array();


   while($row = mysql_fetch_object($result)){
    $tempArray = $row;
    array_push($resultArray, $tempArray);
    }


    echo json_encode($resultArray);

    }
    else{

    echo "failed query";}
    ?>

score 1 · Accepted Answer · edited May 23 '17 at 11:51

In order to do it right, you need to do it on the server side. By the time your data reaches the server, the crucial bit of information about _zip.text.length being zero is gone. All the server knows is that $zipcode variable is set to a string is not null, so it runs the search

SELECT * FROM bas WHERE zipcode='is not null' and parking='is not null'

which does not find anything.

Move the code for length checking to the server side, and pass "raw" strings from the client. Then modify your PHP code to construct the query string based on the input. Start with the base string "SELECT * FROM bas WHERE ", then append either zipcode is not null when $zipcode is empty, or a comparison to $zipcode if it is not empty. After that append " AND ", and do the same for the $parking variable.

Note 1: Your server side implementation lends itself to an easy SQL injection attack. Refer to this Q&A for information on how to fix this problem by parameterizing your query.

Note 2: Using SELECT * in production code is not a good practice. See this Q&A for an explanation of what is wrong with it.

You are absolutely right sir @dasblinkenlight. And I understand that we can do this by your method. But say I have 10 textfields and user can fill all or can fill some textfields. And If I will do it your way then there will be so many conditions. That is why I was trying this logic so that I can solve this by minimum conditions. — Bhupesh Sharma, May 13 '15 at 12:26
@BhupeshSharma Make a function that takes a string and appends a SQL condition. Call it ten times to make a finished SQL. You can also write a different SQL similar to what I described in [this answer](http://stackoverflow.com/a/19875967/335858), but it is a little more involved. — Sergey Kalinichenko, May 13 '15 at 12:32

Unable to pass 'is not null' query to server side

1 Answers1