SQL Server Security – Find the Source of a Recurrent Mass SQL Edit on a Server

First, do your have columns of data type TEXT or NTEXT in your database? If

select 
c.TABLE_NAME,c.COLUMN_NAME 
from INFORMATION_SCHEMA.columns c, 
INFORMATION_SCHEMA.tables t 
where c.DATA_TYPE in ('ntext','text') 
and t.table_name=c.table_name 
and t.table_type='BASE TABLE' 

returns any rows, you probably lost a lot of data during this attack, as the code truncates the existing values to 8000 characters. In that case you need to restore from a backup.

To answer your question, there is no direct way to get to that information from SQL Server. However, using the T-SQL code that was originally intended to be executed (the code that the above code was injected into), you should be able to identify the place in your app where the injection happened. From there you can look through the web site logs to find submissions to that particular page or form that took a long time or that submitted an unusual amount of characters. You can use the columns creation_time and last_execution_time in sys.dm_exec_query_stats to narrow down the time of the attack(s).

Important, once you have the place identified that allowed for the injection to happen, you need to close that gap immediately. You should also review the rest of the application to make sure that no other vulnerable spots lurk in your code. There is a little more information about what to look out for when injection-proofing your app here: http://sqlity.net/en/2547/sql-injection/

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it.

From what I can see, the advantage of using LEFT JOIN in this case is that it removes the attraction for the plan builder to consider this a good thing (which it isn't, in this case), while still letting the plan builder choose from all the other choices. The advantage for the subquery is that you still get the INNER JOIN ability to remove any (rare) invalid rows, but you do not let the plan builder include that fact in the critical plan (because the INNER JOIN is in the outside query, not the subquery). In essence, you are restricting what joins the plan builder can use by placing them in the subquery, and doing the other joins in the main query.

Of course, anytime you restrict the plan builder, you are taking a chance that you restrict it too far and it comes up with a non-optimal plan. Going forward, I won't necessarily use either technique until I find I need to. Note that I also tried to use hints or explicitly tell SQL Server to use a particular type of join, but those were never as fast as when I let it choose everything itself.

In the end, it turned out that, due to the ugliness of the code that built this query (on the fly, even), the LEFT JOIN was far easier to implement and have some confidence that I wouldn't break anything, so I went that way. But that decision was made by factors external to the database.