Blog
Friday, August 03, 2012
Classic ASP - SQL Injection
We still have a few old sites at work that are in classic ASP. One of the problems that tends to occur on these old sites is SQL injection attacks. A lot of old ASP code was written without taking SQL injection into account. It would be great if we could just rewrite all of these sites in ASP.NET, but sometimes the client isn't interested in doing that.
Well, yesterday, we had a production ASP site get hit. This is a site for an old client that I've personally never worked on, so I really knew nothing about it, but I'm getting dragged in, now that we need to clean it up. Looking at the site, I'm actually surprised this hadn't happened earlier.
Looking at the code, I see a few places where we could be doing a better job of input validation. And also a few places where we're doing un-parameterized SQL, which is a big no-no if you want to avoid SQL injection. So, I'm going to try to clean some of that up.
I also want to use URLScan on the server, with some SQL injection rules, to try to get some of this stuff caught at the IIS level. I found this article on how to add some rules to the URLScan.ini file to mitigate SQL injection attacks. (I actually first started reading this article, but then remembered that this particular web server is still on IIS 6.)
When I started poking around on the server, I was surprised to see tha URLScan was already installed. However, it was not configured to do any SQL injection prevention. So, Monday morning, I'm going to try to add the SQL injection rules to the ini file, and see if that breaks anything. Then, I hope to have time to tighten up the code a bit and roll out a new version. I can't say I'm excited to be working on nearly decade-old VBScript, but hey, it'll be good for a few laughs, right?
Well, yesterday, we had a production ASP site get hit. This is a site for an old client that I've personally never worked on, so I really knew nothing about it, but I'm getting dragged in, now that we need to clean it up. Looking at the site, I'm actually surprised this hadn't happened earlier.
Looking at the code, I see a few places where we could be doing a better job of input validation. And also a few places where we're doing un-parameterized SQL, which is a big no-no if you want to avoid SQL injection. So, I'm going to try to clean some of that up.
I also want to use URLScan on the server, with some SQL injection rules, to try to get some of this stuff caught at the IIS level. I found this article on how to add some rules to the URLScan.ini file to mitigate SQL injection attacks. (I actually first started reading this article, but then remembered that this particular web server is still on IIS 6.)
When I started poking around on the server, I was surprised to see tha URLScan was already installed. However, it was not configured to do any SQL injection prevention. So, Monday morning, I'm going to try to add the SQL injection rules to the ini file, and see if that breaks anything. Then, I hope to have time to tighten up the code a bit and roll out a new version. I can't say I'm excited to be working on nearly decade-old VBScript, but hey, it'll be good for a few laughs, right?
Labels: programming, SQL
Wednesday, November 10, 2010
SSMS Tools Pack
Stumbled across the SSMS Tools Pack today. How has this been around since 2007, without me knowing about it? I installed it at work, and I'm already finding it useful.
Labels: programming, SQL
Tuesday, October 26, 2010
found it
Following up on my post from a couple of days back, I found my invalid nulls today. There were null values where there shouldn't have been on 20 records in a table of 100,000+. I fixed them. Tomorrow, I hope to zero in on whatever program bug is causing them to get in there in the first place.
A couple of quick observations:
First, SQL Profiler is my friend. There wasn't really any way I could trace what the VB6 program was doing, and it wasn't putting out enough error info for me to figure it out, but by following a SQL trace, I eventually isolated the problem records.
Second, I discovered Query ExPlus today, which turns out to be a pretty good tool if you need quick access to a SQL query tool on a machine where you can't actually install the full SQL Management Studio.
A couple of quick observations:
First, SQL Profiler is my friend. There wasn't really any way I could trace what the VB6 program was doing, and it wasn't putting out enough error info for me to figure it out, but by following a SQL trace, I eventually isolated the problem records.
Second, I discovered Query ExPlus today, which turns out to be a pretty good tool if you need quick access to a SQL query tool on a machine where you can't actually install the full SQL Management Studio.
Labels: programming, SQL
Wednesday, September 29, 2010
fun with stored procedures
I had some fun today optimizing a stored procedure. It was taking about 10 minutes to run, and I got it down to running in two seconds with a pretty minor change.
Without getting into too many gory details, it was basically doing a somewhat complicated PIVOT. It was using a SUM() call to generate a value that it was really just ignoring, just checking to see if it was null or not. I replaced the SUM() with a COUNT(), and checked for zero instead of null, and bam, 10 minutes down to 2 seconds.
This is the kind of stuff I really like doing -- figuring out how to make a minor change in a bit of code that doesn't affect the output, but results in a measurable improvement in performance. The only thing that's still a little frustrating about this is that I don't completely understand why the difference is so great. I have a general idea of why this worked, but something weird must be going on internally with the PIVOT option in MS SQL for this to have made such a huge difference.
Without getting into too many gory details, it was basically doing a somewhat complicated PIVOT. It was using a SUM() call to generate a value that it was really just ignoring, just checking to see if it was null or not. I replaced the SUM() with a COUNT(), and checked for zero instead of null, and bam, 10 minutes down to 2 seconds.
This is the kind of stuff I really like doing -- figuring out how to make a minor change in a bit of code that doesn't affect the output, but results in a measurable improvement in performance. The only thing that's still a little frustrating about this is that I don't completely understand why the difference is so great. I have a general idea of why this worked, but something weird must be going on internally with the PIVOT option in MS SQL for this to have made such a huge difference.
Labels: programming, SQL
Tuesday, August 10, 2010
stored procedures
I had to debug a problem with a third-party e-commerce system today at work. It took a while to isolate the problem, and when I did, it led me to a certain stored procedure, which turned out to be 7400 lines long. I don't think I'd ever seen a single stored proc quite that big before. Is that normal? I figured out what it was doing by running SQL Profiler, with the "TSQL_SPs" template that shows all the statements being executed within the procedure. With all the if/then logic in the proc, it was really only executing about 1000 lines of SQL for any given run. Still, that's a lot to comb through just to figure out why the logic was returning wholesale prices instead of retail.
I also found a number of lengthy queries in the proc that ended with "and 1=2", which would of course prevent that query from returning any results. I'm wondering why someone would do that rather than comment out the query. It made it fairly difficult to find the queries that were actually executing and returning results.
I also found a number of lengthy queries in the proc that ended with "and 1=2", which would of course prevent that query from returning any results. I'm wondering why someone would do that rather than comment out the query. It made it fairly difficult to find the queries that were actually executing and returning results.
Labels: programming, SQL
Friday, October 31, 2008
more SQL silliness
Well, after thinking I had my SQL problem licked, it came back. I finally got a callback from a support guy at Microsoft, and he had me update statistics on all the tables involved. That fixed things, and everything has been working now for the last couple of days. I'm pretty sure I'd updated statistics on all these tables at least once since the original problem occurred, but maybe I didn't, or maybe I just needed to do it two or three times to get everything back to normal.
I've always had a maintenance plan that updates statistics on all my tables once a week, and I don't think I've ever had problems with statistics before.
I've been reading up on this whole statistics thing now, which I admit I hadn't paid much attention to, until this little debacle. Here's a blog entry by Kim Tripp that explains a few things about the auto-update mechanism, and another one by someone at Microsoft that explains a good bit about the way statistics work in SQL 2005.
I've always had a maintenance plan that updates statistics on all my tables once a week, and I don't think I've ever had problems with statistics before.
I've been reading up on this whole statistics thing now, which I admit I hadn't paid much attention to, until this little debacle. Here's a blog entry by Kim Tripp that explains a few things about the auto-update mechanism, and another one by someone at Microsoft that explains a good bit about the way statistics work in SQL 2005.
Tuesday, October 28, 2008
SQL patching fun
After all the patching I did over the weekend, it seemed like everything was fine on my main SQL server. However, on Monday, we discovered that a couple of oddball SELECT statements weren't working anymore, returning the error "Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
To make a very long story short, I eventually figured out that I could get these queries working again by deleting all of last year's data from one of our bigger tables. The table in question had about 32 million rows in it. I cut it down to 13 million after getting rid of the 2007 data. The moral of this story, apparently, is that you shouldn't try to store 30 million rows in a single SQL Server table. I say this with tongue in cheek, since, obviously, there are other people out there with very large tables in SQL Server. Apparently, I can't be one of them, though.
I put a call in the Microsoft support this morning, but they never called me back. Maybe tomorrow. If they do call back, and I get a support guy who seems to be a bit less clueless than usual, maybe I'll try to see if we can figure out why my server chokes on this table. If I get the usual clueless support dude, I guess I'll just tell him I figured it out on my own while I was waiting for a callback. I wonder if I get my support incident credited back if I solve my own problem while I'm waiting for the callback?
To make a very long story short, I eventually figured out that I could get these queries working again by deleting all of last year's data from one of our bigger tables. The table in question had about 32 million rows in it. I cut it down to 13 million after getting rid of the 2007 data. The moral of this story, apparently, is that you shouldn't try to store 30 million rows in a single SQL Server table. I say this with tongue in cheek, since, obviously, there are other people out there with very large tables in SQL Server. Apparently, I can't be one of them, though.
I put a call in the Microsoft support this morning, but they never called me back. Maybe tomorrow. If they do call back, and I get a support guy who seems to be a bit less clueless than usual, maybe I'll try to see if we can figure out why my server chokes on this table. If I get the usual clueless support dude, I guess I'll just tell him I figured it out on my own while I was waiting for a callback. I wonder if I get my support incident credited back if I solve my own problem while I'm waiting for the callback?
Thursday, September 27, 2007
reference vs. value types, and LINQ
One of the people I work with was having some trouble with reference vs. value types (in C#) this week. This article seems to give a pretty clear explanation of how these things work. Always good to have a little refresher on the fundamentals.
And, in poking around on this guy's web site, I found LINQPad, a tool for executing LINQ queries. I have to admit that I haven't had any time to play around with LINQ at all, but I'm really curious about it. I like his idea of trying to do all your ad-hoc SQL queries in LINQ for a week, to force yourself to start getting used to it. Some of the stuff I'm doing this week is requiring me to do a *lot* of ad-hoc SQL, though. My brain is so completely wired for T-SQL at this point that I think I'd get really frustrated really quickly trying to use anything else.
And, in poking around on this guy's web site, I found LINQPad, a tool for executing LINQ queries. I have to admit that I haven't had any time to play around with LINQ at all, but I'm really curious about it. I like his idea of trying to do all your ad-hoc SQL queries in LINQ for a week, to force yourself to start getting used to it. Some of the stuff I'm doing this week is requiring me to do a *lot* of ad-hoc SQL, though. My brain is so completely wired for T-SQL at this point that I think I'd get really frustrated really quickly trying to use anything else.
Labels: csharp, programming, SQL
Wednesday, February 01, 2006
VS.NET 2005
I just got done installing VS.NET 2005 on my new desktop machine at home. I'm still working my way through ASP.NET 2.0: A Developer's Notebook. I'm just starting the Data Access chapter. The limited version of SQL Server that comes with VS.NET does not have the pubs or Northwind databases installed by default. If you want to install them, you can find setup scripts for them under "C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Samples\Setup", named InstPubs.sql and InstNwnd.sql. To run the scripts, do this:
sqlcmd -S .\SQLEXPRESS -i InstPubs.sql
sqlcmd -S .\SQLEXPRESS -i InstNwnd.sql
That seemed to be worth writing down, just in case I need to do it again...
sqlcmd -S .\SQLEXPRESS -i InstPubs.sql
sqlcmd -S .\SQLEXPRESS -i InstNwnd.sql
That seemed to be worth writing down, just in case I need to do it again...
Tags:
© 2011 Andrew Huey
