SQL Preventing Duplicate Entries if 2 Values Exist

Question

I have a query that I want to check if there are 2 columns with the same data and if so not add data, I will elaborate.

This is my query

INSERT INTO " . TABLE_PREFIX . "installs (
  username,userid,email,addontitle,addonversion,bburl,bbtitle,webmasteremail,cookie,dateline
) VALUES (
  '$username','$userid','$email','$addontitle','$addonversion','$bburl','$bbtitle','$webmasteremail','$cookie',NOW()
  )

I want to check if bburl and addontitle match an entry being submitted and if so not to add that entry. Lets add some example entries

1 --- test@test.com --- Addon --- 1.2 --- test.com --- test --- admin@test.com --- 1 --- ????????
1 --- test@test.com --- Addon2 --- 1.2 --- test.com --- test --- admin@test.com --- 1 --- ????????
1 --- test@test.com --- Addon --- 1.2 --- test2.com --- test --- admin@test.com --- 1 --- ????????

all of these entries would add but now we get another entry:

1 --- test@test.com --- Addon --- 1.3 --- test2.com --- test --- admin@test.com --- 1 --- ????????

test2.com has already installed Addon so I dont want this entry added. How can I prevent this?

As a separate question. If you notice the requested entry I don't want, it seems test2.com was just updating the product, is there a way I can make that modify the existing values and update what has been changed?

you code looks like it is **vulnerable to sql injection**, so [fix that](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) before you start setting the columns that should not have duplicates to **UNIQUE** — nbk, Jun 07 '20 at 11:44
@nbk I have tried breaching this code example I made bburl equal to `value'); DROP TABLE installs;--` this resulted in a database error. I failed to mention this is already in a cms (vBulletin) I'm not sure, but maybe something in the software is protecting it? — DrCustUmz, Jun 07 '20 at 17:43
vbulletin has afaik already a unique on email, look at the table. vbulletin is only a gui, after creating /installing you get a database, and with updates of course the will nbe updates, other wise nithings blocks you from changeing the database, or edit the code see addons. — nbk, Jun 07 '20 at 18:07

Gordon Linoff · Answer 1 · 2020-06-07T14:57:29.670

1

You can create a unique index/constraint on the columns you want to be unique:

alter table t add constraint unq_t_bburl_addontitle unique t(bburl, addontitle)

An insert on the table will then generate an error. You can avoid the error with on duplicate key update:

insert into t ( . . . )
    values ( . . . )
    on duplicate key update bburl = values(bburl);

The update does nothing (because the values already match) but this prevents the code from returning an error.

edited Jun 07 '20 at 14:57

answered Jun 07 '20 at 11:46

Gordon Linoff

1,242,037
58
646
786

would the alter table come after the insert? – DrCustUmz Jun 07 '20 at 13:10
1

@DrCustUmz . . . No. You add the constraint once, usually when the table is created. Then, the database maintains the data integrity that you want (uniqueness in this case). – Gordon Linoff Jun 07 '20 at 13:37
so I have tried running this as a SQL query to `alter table installs add constraint unq_installs_bburl_addontitle on installs(bburl, addontitle)` but it keeps giving me an error `near 'on installs(bburl, addontitle)' at line 1` – DrCustUmz Jun 07 '20 at 14:05

SQL Preventing Duplicate Entries if 2 Values Exist

1 Answers1

Linked