SQL querys for DB table validation

by

Hello there, in this case i show you how to spot bad records on your tables and a handy bad emails detection query. It Should work in most SQL database engines.

Spot sql injection records

SELECT * FROM table where column=”;
SELECT * FROM table WHERE column::text LIKE ‘1%’;
SELECT * FROM table WHERE column::text LIKE ‘(%’;
SELECT * FROM table WHERE column::text LIKE ‘%cast((%’;
SELECT * FROM table WHERE column::text LIKE ‘%@@%’;
SELECT * FROM table where column NOT LIKE ‘%.com’;

Spot bad emails

SELECT * FROM table where (column NOT LIKE ‘%gmail.com’) AND column NOT LIKE ‘%yahoo.com’ AND column NOT LIKE ‘%.net’ AND column NOT LIKE ‘%aol.com’ AND column NOT LIKE ‘%hotmail.com’ AND column NOT LIKE ‘%msn.com’ AND column NOT LIKE ‘%outlook.com’;

Delete detected records

DELETE FROM table WHERE column::text LIKE ‘%cast((%’;
DELETE FROM table WHERE column::text LIKE ‘%@@%’;

Here is an evidence screenshot of an affected table:

In this way you can easily delete corrupted records. Hope it helps you too. if you know any other handy sql query share them in the comments below. Thanks and see you later.

Leave a Comment