Check for existing name in the database

Question

This is my code when I want to add an operation, but it doesn't check for an existing operation that is already in the database, is there any way to do so? code below:

<?php
include('db_conn.php');

$operationname = $_POST['operationname'];
$cost = $_POST['cost'];
$sql = "INSERT INTO operation(operationname,cost) VALUES('$operationname', '$cost')";
$query = mysqli_query($con, $sql);
if($query==true){
    $data = array(
        'status'=>'success',
    );
    echo json_encode($data);
} else {
    $data = array(
        'status'=>'failed',
    );
    echo json_encode($data);
}
?>

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, Sep 06 '22 at 10:23

score 0 · Answer 1 · answered Sep 06 '22 at 10:23

First of all, as @gertjanknappen already mentioned: make operationname unique in the database, it actually looks like a good candidate for being the tables primary key.

That way the database will prevent duplicate inserts. Next what is your actual goal: just preventing duplicate entry, or also updating cost in case a row for operationname already exists?

In the later case

$sql = "INSERT INTO operation(operationname,cost) VALUES('$operationname', '$cost') ON DUPLICATE KEY UPDATE cost='$cost'";

in combination with a unique or primary key constraint on operationmame should give you what you are looking for.

PS: the way you construct your query string from user input is a sure recipe for SQL injection attacks, so you may also want to read up on prepared statement parameter binding, or at least on how to properly validate and escape user input ...

My goal is to prevent duplicate entry in the database, no need to update the cost. it just needs to detect that the entry you entered is already existing in the database and will not proceed to being submitted. PS. will take note and try to improve the syntax and etc to avoid such hacking. — FJV26, Sep 06 '22 at 11:16
In that case either check the returned status code for duplicate key error, or do "SELECT before INSERT" inside a transaction, and only do the insert after select returned zero rows ... — Hartmut Holzgraefe, Sep 07 '22 at 21:09

Check for existing name in the database

1 Answers1