Processing SQL Insertion problems

[JScannell]

My website database has been under a SQL Insertion attack for several months. I have incorporated triggers on every table that has effectively stopped the attacks. Now I want to expand my reporting mechanism to include the IP address of the culprit.

The only way I know how to get an IP address is by making use of the code like this Request.ServerVariables("REMOTE_ADDR" ) in my ASP code.

Is there a way for me to know the IP address of whoever it was that tried to update my table with SQL Insertion links at the time of the update trigger?

Then my reporting would be complete.

Thanks in advance,

Jerry Scannell

 

[markros]

I don't think you would be able to get IP from SQL Server - you can only get it from your application.

PluralSight Learning Library

 

[Crowley16]

I agree with markros, no sql server native code gets ip addresses, however, you can get your front end to log all ip addresses, and then get your trigger to flag the relevant ip address when it fires.

Also, as a suggestion, maybe considering modifying your systems to be more resistant to SQL Injection attacks by using stored procedures and parameter checks rather than use triggers everywhere.
Triggers are useful, but there are downsides, mostly performance related...

--------------------
Procrastinate Now!

 

[JScannell]

Crowley16,

You said "you can get your front end to log all ip addresses, and then get your trigger to flag the relevant ip address when it fires." I am logging all visits the website and placing several of the .ServerVariables into the table. The problem is how can the trigger know which of the recent visit records to look at? Theoretically there could be more than one individual logged into my website at the time of the SQL Insertion. How do I know which of the IP addresses to retrieve?

As far as checking for form entries content: I have already put those checks into place, however, by the time I did the culprit figured out what tables I have and which columns to update, so he is able to attempt his actions without first logging into a data form, I think.



Jerry Scannell

 

[Crowley16]

you can disable direct sql access to your database, thus forcing anyone without direct admin access to only use stored procedures.
might be worth starting another thread in sql setup/admin thread to discuss exactly how this should be done...

as for deciding which ip is relevant, that depends on how your triggers are currently detecting attacks. If they are scanning just the inserted / deleted internal tables, then can you not just log the ip address captured for that particular insert/update/delete statement?

--------------------
Procrastinate Now!

 

[SQLSister]

First some required reading:

http://www.unixwiz.net/techtips/sql-injection.html

http://www.sommarskog.se/dynamic_sql.html

You can add a column to the tables to store the ip address of the person last updating the record, you application would then send this ip. YOu trigger can take that value from the inserted table and store it elsewhere when a record is rejected for trying an attack.

However, it is a better policy to prevent attacks from happening than to use a trigger to roll them back. That's why you need to read the required reading.

"NOTHING is more important in a database than integrity." ESquared