MySQLi condition not properly working

Question

When user try to register, I am checking first table device, if there any device_id available I want provide user trial=0 and if there no any device_id, I want insert device_id in that table and want user trial=1, but currently its always set trial=1 and inserting device_id in table device. My current code is like below

$serial = $POST["serial"];
    $fcm = $POST["fcm"];
    $trial  = 0;
    $trial_sql = "SELECT FROM device WHERE device_id = $serial";
        $trial_result = mysqli_query($conn, $trial_sql);
        if (mysqli_num_rows($trial_result) == 0) {
         $device_sql = "INSERT INTO device(device_id) VALUES('$serial')";
         if($conn->query($device_sql)){
             $trial = 1;
            }   
    }
    $sql = "INSERT INTO user(name, email, password, device_id, trial, fcm) VALUES('$name', '$email', '$password', '$serial', $trial, '$fcm')";
    if($conn->query($sql)) {
        $response["code"] = 1;
    }
    return json_encode($response);

Let me know if someone can correct my mistake. Thanks

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, Apr 12 '18 at 14:25
@AlexHowansky Thanks for your comment and suggestion. I will do it before I am make live. Let me know if You can help me for solve my located issue. Thanks — Khushi Patel, Apr 12 '18 at 14:27
You should also make sure that the `device_id` field has a unique constraint in the database schema. — Alex Howansky, Apr 12 '18 at 14:28
If device_id is a string, you would need quotes round the value n the select (or use prepared statements) — Nigel Ren, Apr 12 '18 at 14:36

score 1 · Accepted Answer · answered Apr 12 '18 at 14:49

1

You both need to specify a column to select as well as include quotes round the device_id...

$trial_sql = "SELECT * FROM device WHERE device_id = '$serial'";

The quotes would have not been a problem if you used prepared statements.

answered Apr 12 '18 at 14:49

Nigel Ren

56,122
11
43
55

Turn the tide against teaching/propagating sloppy and dangerous coding practices. If you post an answer without prepared statements [you may want to consider this before posting](http://meta.stackoverflow.com/q/344703/). Additionally [a more valuable answer comes from showing the OP the right method](https://meta.stackoverflow.com/a/290789/1011527). – Jay Blanchard Apr 12 '18 at 14:55

score 0 · Answer 2 · answered Apr 12 '18 at 14:26

0

SELECT * FROM device

Or

SELECT columnA, columnB FROM device

Your first query is not valid because it doesn't say what columns it wants to select from device table.

answered Apr 12 '18 at 14:26

Nerdi.org

895
6
13

Hi! Its still same issue. – Khushi Patel Apr 12 '18 at 14:33
You need to do "SELECT device_id FROM..." in that first one then – Nerdi.org Apr 12 '18 at 14:34
I havent used non-prepared statements in a while now. I stick to prepare, bind params, bind result, and execute. It makes it very easy to see where everything goes and works. http://wbr.bz/QueryPro is a good place to start – Nerdi.org Apr 12 '18 at 14:36

MySQLi condition not properly working

2 Answers2