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, Jun 21 '13 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, Jun 21 '13 at 18:15

score 1 · Answer 1 · answered Jun 21 '13 at 18:16

1

try this with group by

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

answered Jun 21 '13 at 18:16

echo_Me

37,078
5
58
78

the funny part it that without the group by also should be able to check right? – new14 Jun 21 '13 at 18:17
what you mean by use php? I am running it on php ? So what else could be failing it something strange rite. – new14 Jun 21 '13 at 18:34
1

i mean you can also filter duplicates by php i know you are using php :) – echo_Me Jun 21 '13 at 18:40
How to filter duplicates by php I thought by looking via the select statement should be helpful but I notice it is working fine on my side but when my clients do it by pass that check. That is the strange part . – new14 Jun 21 '13 at 18:42
1

i gave you my answer to dont get duplicates it doesnt help ? – echo_Me Jun 21 '13 at 18:43
I will try that too but what I dont understand is that what is the wrong with my existing query can you point out on that ? – new14 Jun 21 '13 at 19:10
1

your query , you forgot GROUP BY statment – echo_Me Jun 21 '13 at 19:19
1

@echo_Samir can you explain why adding `GROUP BY` should help? – dev-null-dweller Jun 21 '13 at 20:56
YEs how will the group by be a help and why the basic query will fail any reason please? – new14 Jun 22 '13 at 14:49

score 1 · Accepted Answer · answered Jun 21 '13 at 19:02

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 '13 at 19:02

dev-null-dweller

29,274
3
65
85

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() ? – new14 Jun 22 '13 at 14:54
You are using `mysqli` in your code, so if you insist on not using prepared statement, use `mysqli_real_escape_string`. [Here](http://stackoverflow.com/q/60174/258674) 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. – dev-null-dweller Jun 22 '13 at 16:45
I have been using mysqli_real_escape_string it in my other pages. IS it safe enough or should I move to prepaered statements? – new14 Jun 22 '13 at 18:59

score 1 · Answer 3 · answered Jun 21 '13 at 19:15

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 '13 at 19:15

Aris

4,643
1
41
38

why and what is wrong with the query infact ? I am quite curious why it works on my side ? – new14 Jun 22 '13 at 14:49
1

probably nothing is wrong. just suggesting a way to avoid an extra query to the database – Aris Jun 23 '13 at 08:15
what I am curious how come such a simple query could have failed? – new14 Jun 23 '13 at 10:43
the select query didn't fail. but if the registration number is not a unique key, is very easy to insert duplicates. – Aris Jun 23 '13 at 21:45
When you say very easy means in what scenario it might have failed? – new14 Jun 24 '13 at 16:46
As a general rule, yes it's very possible for the php logic to fail. If you can ensure your data integrity from the database, is the best method. You avoid redundant code in PHP, and also avoid many possible bugs. – Aris Jun 25 '13 at 20:14
What could fail it as its just a simple logic check right? Because I have this sort codes checking many other things too say my login page etc. – new14 Jun 29 '13 at 16:27

How avoid duplicate data entry via php to mysql?

3 Answers3