SQL - Why I have a duplicated row if im checking that unique id exists?

Question

I found a duplicated row in my table > image from my table

But in my code, I check if "unique_id" key exists, then update this row, else, create.. I am very noob with sql and I made some functions to make more easy my life... this is my "send_score.php" file

<?php
header('Content-Type: text/plain');
header("Access-Control-Allow-Origin: *");

include "../../functions.php";

$servername = "xxx";
$database = "xxx";
$username = "xxx";
$password = "xxx";
$tabla = "xxx";

$unique_key = $_POST['unique_key'];
$nick = $_POST['nick'];
$puntos = $_POST['puntos'];

$con = sql_connect($servername, $username, $password, $database);
    if ( sql_check_row($con,$tabla,"unique_key","'".$unique_key."'") ) {
        //if exists, replace
        if ( sql_update_row($con,$tabla,"nick",$nick,"unique_key",$unique_key) && sql_update_row($con,$tabla,"sc",$puntos,"unique_key",$unique_key) ) {
            echo "OK UPDATE";
            exit;
        }
        else {
            echo "BAD UPDATE";
            exit;
        }
    }
    else {
        //if not exists, create
        $arr_key = array("unique_key","nick","sc");
        $arr_val = array("'".$unique_key."'","'".$nick."'",$puntos);
        if ( sql_insert_row($con,$tabla,$arr_key,$arr_val) ) {
            echo "OK INSERT";
            exit;
        }
        else {
            echo "BAD INSERT";
            exit;
        }
    }
sql_close($con);

?>

and here is the functions that im using here

function sql_connect($servername, $username, $password, $database) {
    return mysqli_connect($servername, $username, $password, $database);
}

function sql_close($con) {
    return mysql_close($con);
}

function sql_check_row($con,$table,$key,$value) {
    $sql = mysqli_query($con, "SELECT * FROM ".$table." WHERE ".$key." = ".$value);
    if( mysqli_num_rows($sql) ) {
        return true;
    }
    else {
        return false;
    }
}

function sql_update_row($con,$table,$key_set,$value_set,$key_where,$value_where) {
    $sql = mysqli_query($con,"UPDATE ".$table." SET ".$key_set." = '".$value_set."' WHERE ".$key_where." = '".$value_where."';");
    if ($sql) {
        return true;
    }
    else {
        return false;
    }
}

function sql_insert_row($con,$table,$key_arr,$value_arr) {
    $keys = "(";
    for ( $i = 0; $i < sizeof($key_arr); $i++ ){
        if ($i != 0) {
            $keys .= ",";
        }
        $keys .= $key_arr[$i];
    }
    $keys .= ")";
    
    $query = "INSERT INTO ".$table." ".$keys." VALUES (";
    for ( $i = 0; $i < sizeof($value_arr); $i++ ){
        if ($i != 0) {
            $query .= ",";
        }
        $query .= $value_arr[$i];
    }
    $query .= ");";
    
    $sql = mysqli_query($con,$query);
    if ($sql) {
        return true;
    }
    else {
        return false;
    }
}

According to me, it shouldn't create a new row because a row already exists with the same "unique_id" and in the image, both rows have exactly the same "unique_id"... why???

**Warning!** You're open to [SQL injection attacks](https://owasp.org/www-community/attacks/SQL_Injection)! Read [how to prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) by using prepared statements with bound parameters instead of injecting variables directly into your queries. It's not just about security. If your data contains, for example, a single quote `'`, your query will break. — M. Eriksson, Aug 13 '22 at 07:38
`return mysql_close($con);` - I think you meant `mysqli_close()`. The extensions `mysql_*` (which was completely removed in PHP 7) and `mysqli_*` are two completely different extensions and can't be mixed. — M. Eriksson, Aug 13 '22 at 07:41

score 0 · Answer 1 · answered Aug 13 '22 at 08:16

A simply strategy here is to write the values of column "unique_key" in an array and then test if the new value exist with "in_array()". If the value exists you update. If not you insert a new row:

 <?php
  $all_unique_ids = array();
  $con = sql_connect($servername, $username, $password, $database);
  $stmt = $con->prepare("SELECT `unique_key` FROM `".$tabla."`");
  $stmt->execute();
   foreach ($stmt->get_result() as $row)
  {
   $all_unique_ids[] = $row['unique_key'];
  }
  mysqli_close($con);

   if(in_array($unique_id, $all_unique_ids))
  {
  sql_update_row(.....);    
  }
  else
  {
  sql_insert_row(   ......);    
  }
  ?>

score 0 · Answer 2 · answered Aug 19 '22 at 23:34

Your code has a race condition. Two requests to the same page at the same time with the same unique_id will check the database at the same time and both get back the same result. Namely that the given unique_id is not in the database.

Both requests will then each INSERT a row with the same unique_id. This is why databases allow you to give a column a unique constraint.

SQL - Why I have a duplicated row if im checking that unique id exists?

2 Answers2