Android connecting to mysql using php, String query error

Question

I am writing an android program which use http post to connect to a php script to connect to mysql database. query is the variable to be sent over.

Here is part of my code:

String query = "select `retailer`.`name` as `retailer`, `product`.`name` as `product`, `product`.price, `promotion`.discount, `promotion`.discount_type";
        query = query.concat(" from `retailer`, `consumer`, `product`, `promotion`, `sell`, `proximity`");
        query = query.concat(" where `consumer`.`email` = '"+consumer_email+"'");
        query = query.concat(" and `proximity`.`retailer_id` = `retailer`.`id`" +
                " and `proximity`.`consumer_id` = `consumer`.`id`" +
                " and `sell`.`retailer_id` = `retailer`.`id`" +
                " and `sell`.`product_id` = `product`.`id`" +
                " and `product`.`promotion_id` = `promotion`.`id`");

Note that consumer_email is a string containing the email i want to query. However i keep getting syntax error with this. if i take away the line consumer.email = '"+consumer_email+"'", it is able to process with no error.

What i want to ask if there's any way to solve this problem? Is it the single quote surrounding the email that is causing the problem?

what error are you getting? What returns the error, the php script, the sql server, the android program? — Nanne
– Nanne, Commented Dec 29, 2011 at 19:55
Wow, you just implemented a feature called SQL Injection in it's bare meaning. You're lucky that this breaks so early, because your really have to throw your code away and change the way how you tell the PHP script which data to obtain by parameters itself (not an SQL query!) — hakre
– hakre, Commented Dec 29, 2011 at 19:55
Probably my last comment was a bit unfriendly, you might be looking for this: MySQL over HTTP — hakre
– hakre, Commented Dec 29, 2011 at 19:58

kensen john · Accepted Answer · 2011-12-29 20:38:12Z

1

Use prepared statement like this.

This will eliminate the error you are seeing. It is also secure and prevent SQL Injection hacks.

Instead of directly replacing the email address in the query, use the "?". Then bind values to it at run time.
http://php.net/manual/en/pdo.prepared-statements.php

   String query = ......<rest of query>
     query = query.concat(" where `consumer`.`email` = ? );
    ......<rest of query>

    $db = new mysqli('localhost', 'username', 'password', 'database');
    // Create statement object
    $stmt = $db->stmt_init();


// Create a prepared statement
if($stmt->prepare(query )) {
    $stmt->bind_param('s', $email_id);

    // Set your variable    
    $email_id= "[email protected]";

    // Execute query
    $stmt->execute();
}

answered Dec 29, 2011 at 20:38

kensen john

5,5195 gold badges31 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

Android connecting to mysql using php, String query error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related