March 14, 2016 at 5:12 pm
A couple of the guys in the office have been seeing what looks to be html email code being appended to some fields via updates on some tables in one of our databases. This is only occurring with newly created tables. Anyone have some idea as to how to work on solving this issue? I'm starting a sql server profiler trace to look at the inserts.
Thanks
March 15, 2016 at 6:28 am
Not enough info to go on. Trace, or extended events, is the way to go to capture the queries that are being called. The real question is, is the server locked down? Are you giving overly broad security settings to the logins? Are all queries going through parameterized mechanisms, stored procs or client-side parameterized queries? If your security is off and/or you're letting completely ad hoc queries with no data type validation occur, time to fix things.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2016 at 9:41 am
Find the places where the table is being updated. Those are likely vectors for SQL injection.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 15, 2016 at 9:46 am
Grant Fritchey (3/15/2016)
Not enough info to go on. Trace, or extended events, is the way to go to capture the queries that are being called. The real question is, is the server locked down? Are you giving overly broad security settings to the logins? Are all queries going through parameterized mechanisms, stored procs or client-side parameterized queries? If your security is off and/or you're letting completely ad hoc queries with no data type validation occur, time to fix things.
I'll set up the extended events.
1. Server is pretty locked down, however I can see some potential points of security risk
2. Yes, there are some broad security settings
3. Nope, not parameterized and there are many ad-hoc queries
Unfortunately, changing 2 and 3 is going to be near impossible to do and I have talked to them about doing this. For now I'd like to at least find the point of sql injection.
March 15, 2016 at 9:56 am
How are the tables updated? Custom app on the web? Some framework?
Likely you have someone building a statement in code, as
cmdExecute = 'update mytable set col = ''' + @value + ''' where pk = ''' + @key
Then someone tags a value a [; update othertable set mycol = somevalueWithHTML] as part of the submission. This is classic injection. There is cross site scripting and second level injection as well, but they almost always come about from building a T-SQL statement here. You might find that the table is being updated from places that don't normally update that table (See above), which is why your code should *always* parameterize the values.
I doubt this is coming as part of your overall security, but it could be. However I'd focus with extended events to find the places where this table(s) is updated and then examine code.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 15, 2016 at 9:57 am
joshdbguy (3/15/2016)
Grant Fritchey (3/15/2016)
Not enough info to go on. Trace, or extended events, is the way to go to capture the queries that are being called. The real question is, is the server locked down? Are you giving overly broad security settings to the logins? Are all queries going through parameterized mechanisms, stored procs or client-side parameterized queries? If your security is off and/or you're letting completely ad hoc queries with no data type validation occur, time to fix things.I'll set up the extended events.
1. Server is pretty locked down, however I can see some potential points of security risk
2. Yes, there are some broad security settings
3. Nope, not parameterized and there are many ad-hoc queries
Unfortunately, changing 2 and 3 is going to be near impossible to do and I have talked to them about doing this. For now I'd like to at least find the point of sql injection.
Ouch. Might be time to point out all the failed businesses and lawsuits based on the security breaches from SQL Injection. It's crazy that this is still an issue after all these years.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 15, 2016 at 10:10 am
Are you sure that it's actually SQL injection and not just an issue with whatever application is writing to those tables? Putting HTML code in a varchar field isn't necessarily SQL injection it could just be a badly coded application.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply