SQLite INSERT OR REPLACE without a constraint

I learned something new yesterday while working on a simple four column table that keeps a list of the favorite tools in NetScanTools Pro. This table was set up without a constraint meaning there is nothing stopping us from inserting a row that is identical to a current row. Here is the DDL for the table:

CREATE TABLE [favorites] (
[col1] NUMBER,
[col2] TEXT,
[col3] NUMBER,
[col4] NUMBER);

As you can see - no constraints.

While working with the right click menu that adds a tool to NetScanTools Pro I discovered that it was adding the same row twice. I was using an INSERT INTO favorites... SQL command. So I thought I would get rid of the duplicates by using INSERT OR REPLACE INTO favorites. Wrong.

What I didn't know/remember was that the OR REPLACE means to replace the rows that are causing a constraint violation. Since there is no constraint, there is no constraint violation meaning the OR REPLACE clause does nothing and multiple identical rows can still be inserted.

An obvious solution is to change the table and add a constraint like this:

CREATE TABLE [favorites] (
[col1] NUMBER,
[col2] TEXT,
[col3] NUMBER,
[col4] NUMBER,
CONSTRAINT [] PRIMARY KEY ([col1]) ON CONFLICT REPLACE);

But to do so in an installed base of software in the hands of customers would require changes: ie. copying the existing table, deleting the old table and recreating it with the constraint. Plus it would require checking every time the program is started to make sure it had been done or some other versioning method would have to be used. Lots of details.

I arrived at a different solution using a TRIGGER to remove duplicate rows before the INSERT happens. This requires no changes to the design of the table and does not require changing anything in the user's database. This statement creates a TEMPORARY TRIGGER if it does not already exist and does this before an INSERT happens. It deletes any rows that match 'new' col1, then does the INSERT.

CREATE TEMP TRIGGER IF NOT EXISTS [unique_row]
BEFORE INSERT
ON favorites
BEGIN
DELETE FROM favorites WHERE new.col1= col1;
END

Pretty simple and appears to get the job done without modifying the original table. TRIGGERS are very powerful and I will probably be using more of them in the future - but using them with care.

Thanks to Bogdan Ureche for help in understanding what was going on with INSERT OR REPLACE without a corresponding CONSTRAINT.

www.SQLite.org

NetScanTools in Hacking for Dummies 4th Edition

Kevin Beaver wrote Hacking for Dummies and he has just completed the 4th edition. It's a well written comprehensive book on Ethical Hacking. He talks about hacking network hosts, operating systems, applications (communications, databases and websites), password cracking all from the perspective of a security consultant with permission to find the weaknesses in the targets. This can help you understand the dangers your computers or your business systems face from malicious hackers.

NetScanTools Pro is one of the tools he uses to demonstrate how you can assess vulnerabilities. While NetScanTools Pro is not designed as a hacking tool, it does have some tools that you can use to test for vulnerabilities.

Examples used in the book from NetScanTools Pro:

Using Ping Scanner to ping multiple IP addresses to find out what hosts are accessible.
Finding open ports with Port Scanner like the port 53 DNS, NetBIOS port 139, and SQL Server port 1434.
Scanning SNMP or dumping whole system's MIB trees using our SNMP tools.
Using the UDP Packet Flooder as a DoS testing tool.
Windows share enumeration using our Network Shares - SMB tool.
SMTP Relay Testing.

Get the book at dummies.com. ISBN 978-1-118-38093-2

NetScanTools LE 1.51 Released

NetScanTools LE was updated to ensure Windows 8 compatibility. Some minor user interface changes were made, documents and databases updated. Whois now has better support for looking up IPv6 addresses.

Please visit http://www.netscantools-le.com/ for more information.