How safe is following code for creating mysql - yearly resetting field, safety also considering input from multiple requests

Question

I HAVE tables in this test code:

table WORKER -> name varchar(50) , spec varchar(50) UNIQYE

table KEYGEN- > id INT AUTO_INCREMENT , year varchar(50)

spec should be like = 2009/54.

CODE: for checking and reseting (truncating) keygen table.

$insertedid=""; //to put last inserted id from keygen 
$spec="";   //spec number generated from year + $insertedid in table keygen
$year=date("Y");  //current year

$query="SELECT * from keygen WHERE year='{$year}' ";

    if($noviRed=$mysqli->query($query))
    {
        if($noviRed->num_rows==0)
        {
            $truncate="truncate keygen"; 
                if($mysqli->query($truncate)) {echo"<h2> TRUNCATING  TABLE OK </h2>";}  else { exit("fatal error");}

            $query2="INSERT INTO keygen(year) values('{$year}') ";
                if($mysqli->query($query2)) {$insertedid=$mysqli->insert_id;} else {exit("fatal error");}
        }
        else
        {

            $query2="INSERT INTO keygen(year) values('{$year}') ";
                if($mysqli->query($query2)) {$insertedid=$mysqli->insert_id;} else {exit("fatal error");}
        }
    }
    else {exit("fatal error");}

CODE: for inserting data into worker table.

$spec=$year."/".$insertedid; //generating spec number

if($insertedid!="" AND $spec!="")   
{
    $query3="INSERT INTO worker(name,spec) values('{$name}','{$spec}') ";   

    if($noviRed=$mysqli->query($query3)){echo"<h1>SUCCES </h1>";}
    else {exit("fatal error");}
}

the code isn't safe at all and is open to an sql injection. Use a prepared statement. — Funk Forty Niner, Jul 27 '16 at 14:29
@Fred-ii- Shown code doesn't use any user input. `mysqli->insert_id` and `date("Y")` always ints. How is it possible to make SQL injection here? — Arnial, Jul 27 '16 at 14:37
Any time an unprepared variable is forced into a query there is an inherent danger @Arnial. — Jay Blanchard, Jul 27 '16 at 14:39

score 1 · Answer 1 · answered Jul 27 '16 at 15:23

1

Not entirely safe.

If multiple requests come in at once near the beginning of a year, the TRUNCATE query may end up running twice, as the SELECT will return zero rows for both requests. This would probably result in a unique key collision on one of the subsequent inserts.

answered Jul 27 '16 at 15:23

Can this be avoided by using Prepared Statements ? – Dime Jul 27 '16 at 15:58
No. This is a completely separate issue from SQL injection. – Jul 27 '16 at 16:02
what should i do to avoid truncate running twice? – Dime Jul 27 '16 at 21:28

How safe is following code for creating mysql - yearly resetting field, safety also considering input from multiple requests

1 Answers1