Duplicate Records & CFTRANSACTION

Forum|Forum|17 years ago
August 6, 2008
4 replies
1157 views

Maybe I'm missing the obvious on this, as I've never had a problem with this before, but recently I developed a custom tag that logs and blocks the IP addresses of all these recent DECLARE SQL injection attempts.

The issue is however that my blocked IP address seems to be getting duplicates here and there. My "datetime" field in the database show the duplicates are all added to the database the exact same second. What gives?

Shouldn't CFTRANSACTION be preventing such a thing, even if multiple injection attempts come at the same time?

This topic has been closed for replies.

S

Stressed_Simon

Inspiring

I assume you are using SQL server, so why don't you use a stored procedure which can handle it's own transactions correctly and can have as much logic in it as necessary.

N

Newsgroup_User

Inspiring

> <if sql injection detected>

At this point, why are you bothering to do *anything* other than throwing
an exception?

--
Adam

D

Dan_Bracuk

Inspiring

If you don't want duplicate IP addresses, why didn't you make that the primary key?

in any event do a query like this:

insert into yourtable
(field1, field2, etc)
select distinct value1, value2, etc
from some_small_table
where
(select count(field1)
from yourtable
where ip_address = the_one_you_are_trying_to_add) = 0

C

christopherkowalskiAuthor

Inspiring

I've always coded my applications where my primary key is my database's autonumber field, and instead insure my coding is solid enough to prevent duplicate records from appearing. Oddly enough it's worked flawlessly until now.

Would not ColdFusion throw errors if I made the "ip_address" field my primary key, and my code allowed for a duplicate record to be entered? Am I interpretting the CFTRANSACTION code to do something it doesn't do?

Also, the duplicates aren't causing problems, so a DISTINCT select isn't necessary. The IP address is blocked whether one record or fifty exist in my blocked_ip_addresses table. My goal is just not to waste database space.

Any further help you can provide is MUCH appreciated! Thanks!

D

Dan_Bracuk

Inspiring

quote:

Originally posted by: ckbentdesigns

Would not ColdFusion throw errors if I made the "ip_address" field my primary key, and my code allowed for a duplicate record to be entered?

Probably. Fortunately there are cftry/cfcatch tags available to handle it.

D

Dan_Bracuk

Inspiring

What's the primary key of this table?