How avoid duplicate data entry via php to mysql?

Question

We have a table vehicle and a simple php form. Before inserting data I do check if the vehicle registration number exist but some client pc could enter duplicate entries. Below is the code snippet. What else could be causing this ?

$vehicleRegistrationNumber=$_POST['vehicleRegistrationNumber'];
$selectQuery1 ="Select vehicleRegistrationNumber From Vehicle Where vehicleRegistrationNumber='".$vehicleRegistrationNumber."'"; 
$result1 = mysqli_query($link,$selectQuery1);
$row1 = mysqli_fetch_array($result1, MYSQL_ASSOC);
$n1 = mysqli_num_rows($result1);

if($n1 > 0) {
  $status="<span class=\"statusFailed\">: Vehicle ".$vehicleRegistrationNumber." Already Exist.</span>";
}
else {
  //insert codes
}

Why don´t just use UNIQUE index on vehicleRegistrationNumber? That way mysql will handle it for you and you just have to take care about the error you will get if you try to insert duplicate row. — hynner
– hynner, Commented Jun 21, 2013 at 18:07
Yes I have now done that on the UNIQUE INDEXING but what could be the problem via the browser that this check failed? — new14
– new14, Commented Jun 21, 2013 at 18:15

dev-null-dweller · Accepted Answer · 2013-06-21 19:02:20Z

1

First of all your code is vulnerable to SQL injection. This check can be bypassed by submitting something like XYZ0001' AND 1='0 or even more malicious values. To prevent this, use prepared statements and param binding instead of string concatenation.

Other possibility is simply user mistake, for example trailing space ("XYZ001" != "XYZ0001 ") that is hard to spot on the first glance ad records in DB. Before checking its existence in DB you should check with PHP if submitted value includes only allowed chars and is free from common mistakes.

answered Jun 21, 2013 at 19:02

dev-null-dweller

29.5k3 gold badges68 silver badges87 bronze badges

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

3 Comments

new14 Over a year ago

so I got few other codes where I do this sort of check. So how to avoid them or strengthen on their security? So how avoid the common mistakes in php and over come on the sql injection will this be a help fullful if I put them in mysql_real_escape_string() ?

dev-null-dweller Over a year ago

You are using mysqli in your code, so if you insist on not using prepared statement, use mysqli_real_escape_string. Here you can read more about sql injections. As for common mistakes you have to define patterns for desired data and check if it fits your requirements, and if not, prompt the user with desired format.

new14 Over a year ago

I have been using mysqli_real_escape_string it in my other pages. IS it safe enough or should I move to prepaered statements?

echo_Me · Accepted Answer · 2013-06-21 18:16:00Z

1

try this with group by

 $selectQuery1 ="Select vehicleRegistrationNumber From Vehicle Where vehicleRegistrationNumber='".$vehicleRegistrationNumber."' GROUP BY vehicleRegistrationNumber";

answered Jun 21, 2013 at 18:16

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

9 Comments

new14 Over a year ago

the funny part it that without the group by also should be able to check right?

echo_Me Over a year ago

i mean you can also filter duplicates by php i know you are using php :)

echo_Me Over a year ago

i gave you my answer to dont get duplicates it doesnt help ?

echo_Me Over a year ago

your query , you forgot GROUP BY statment

dev-null-dweller Over a year ago

@echo_Samir can you explain why adding GROUP BY should help?

|

Aris · Accepted Answer · 2013-06-21 19:15:27Z

1

The best way is to handle it on the SQL side. Just define the field as UNIQUE INDEX.

Now when trying to insert a duplicate index an error will be thrown and you can catch it like this:

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error($con));
  }

Like this you can avoid the select query before every insert query. Just handle the error as you want.

answered Jun 21, 2013 at 19:15

Aris

5,1131 gold badge47 silver badges42 bronze badges

7 Comments

new14 Over a year ago

why and what is wrong with the query infact ? I am quite curious why it works on my side ?

Aris Over a year ago

probably nothing is wrong. just suggesting a way to avoid an extra query to the database

new14 Over a year ago

what I am curious how come such a simple query could have failed?

Aris Over a year ago

the select query didn't fail. but if the registration number is not a unique key, is very easy to insert duplicates.

new14 Over a year ago

When you say very easy means in what scenario it might have failed?

|

Collectives™ on Stack Overflow

How avoid duplicate data entry via php to mysql?

3 Answers 3

3 Comments

9 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

9 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related