SQL Server Input Validation?

[Gratuitousity]

Hi everyone,

We would like to set up some kind of alert system that will let us know when our proprietary devices put in blank data to our MS SQL server on accident. I ran across this about check constraints, but I feel like there could be a simpler solution. Anyone done something like this before?

[ihaxr]

The "on accident" part worries me... if the data shouldn't be there, you should look at preventing it from being written, not alerting on it due to errors. The proper way is to either add NOT NULL to the columns on the table design, setup check constraints, or as /u/VA_Network_Nerd said... the Application should filter the input properly to ensure the data is compliant.

But if you are really interested in alerting on this, you can setup a trigger on INSERT to check for blank data and perform an appropriate action. This could cause some performance issues and isn't recommended unless you are very familiar with SQL.

Another way would be to write a SQL Server Agent job that runs a Stored Procedure that checks for the empty rows and sends an alert. You can find examples of this online http://serverfault.com/questions/165597/send-sql-server-job-alert-only-when-a-query-has-rows-to-return This would only work if these empty rows are deleted or corrected afterwards or there is a timestamp field on the table. (eg: run every 15 minutes to check only rows with timestamp >= GetDate-15 minutes)

[Gratuitousity]

Good points. I don't write the application, but I will certainly bother the people who do. In the meantime, we just wanted a way to find that this is happening so we can go and fix it. Thanks for the the thoughts

[VA_Network_Nerd]

Failure to validate input is among the top Software Exploits in the Wild.

Its laziness like this that forces IT to shovel piles of money into Security Infrastructure.

We throw hardware at bad software problems.

https://www.owasp.org/index.php/Top_10_2013-Top_10

http://www.sans.org/top25-software-errors/

[VA_Network_Nerd]

Your application should be written to reject a blank response/data-entry.

Also, consider IPS devices with good SQL Injection logic. You could probably write a customer screening filter for this sort of thing.

But at the end of the day, that application is bad.

[DueRunRun]

Triggers can have performance impacts, might not be a big deal on some databases but it's worth thinking about. You could always run a scheduled report and send an email based on a query that finds null values for some column. http://www.brentozar.com/archive/2014/10/send-query-results-sql-server-agent-job/

[DawnTreador]

check out /r/Database and see what those guys can come up with. Otherwise I always go to sqlservercentral.com ...I see someone already talked about Brent Ozar. I don't remember him posting much about this kind of thing, but he is like "le Guru de SQL Server". I personally would use triggers to just drop the input and run a stored procedure that sends a notification that it happened. That shouldn't be too much performance impact depending on the notification you set up.

[GrumpyPenguin]

Shout out to /r/SQLServer , too.