How Secure is Secure?
It wasn’t more than a few years ago when Microsoft was the whipping kid when it came to inadequate security
measures. Apple, Unix/Linux, Solaris, OS 400, MVS, CMS, etc. were considered more secure because they either
didn’t have enough market share, or had less vulnerabilities.
Less focus was paid to our own practices in writing code or configuring and managing systems. Third party software
vendors were often excluded from deep scrutiny. That is not to say that many companies have not taken the matter seriously. However, I am confused why I continue to hear stories of things that are simple to solve, such as SQL
Injection, or backdoor access to internet based resources.
Rather than incur the cost of hiring adequate professionals to maintain internal systems security ,we have started
moving to software as a service. This way we can offload the security of our software and business resources to a
third party. But wait, how many alarms and failures have we had in the Cloud just this year alone. Can you afford to
have your data offline, be unable to access your applications, or have your credit accounts hacked?
Today the news about Dropbox, a rapidly emerging company with remote storage, reveals a security breach for
four hours, where anyone knowing your Email address would have been able to access your files. Isn’t this a big deal? Simple software deployment processes could have prevented this situation. It only took them 5 minutes to fix the bug;
but four hours to detect it? Dropbox is considered one of the bigger players with online distributed file replication.
How do you gain assurance that a company exercises good security practices? Sure, there are different certifications
a company may have such as SAS70, PCI, SOX, HIPAA, etc. I’ve been through a few of those audits…they are a huge step in the right direction. But a company can be bleeding with security holes and pass some of those audits.
I’m wondering if this will require changes from consumers of CLOUD software. Will the tightly written legalize of the
Software as a Service providers cause customers to go elsewhere? Will customers begin to require higher levels of
service including financial penalties? The whole point is to save money, right? Well, if your business data is stolen,
lost or unavailable, what did you save?
How do we protect ourselves and the companies we support? I’d love to hear from our readers regarding
requirements they have for internal systems, software, third party software hosted locally, and software as a service.
More SQL Injection Replies
Brian:
Off the top of my head:
1) Refrain from using max. Put a character limit on the parameter.
2) Put a character limit on the input field of the client.
3) Add client-side validation to only take alphanumeric characters.
Michael:
I’ve had experience with the same issues. The way I approached it was not a technical solution.
(1) Mostly it was keeping the existence of the problem quiet and only those that needed to know knew
(even internally). Basically "Security through obscurity" until we could patch things up properly.
(2) lock down the account that has access to spUpdateTable. Granting only those permissions that are needed.
(3) Monitor error logs and exceptions really closely. A SQL injection attack is usually accompanied by a lot of
syntax errors.
(4) preparing and testing the fix. (Even though it was a large amount of work)
Steve:
Something that comes to mind since they are using a single posting point with that stored procedure. Scrubbing it
internally on the stored procedure might be the simplest approach. Truncate anything after the first semicolon
would be my first choice. Ideally they’d have to ensure that nothing exists that would be adding a semicolon
but that would seem to be a minor issue to deal with.
Something I’ve also done since I didn’t want to walk through hundreds of lines of old code to change the method
is to put security up front with rewrite rules and redirects that exclude something from posting with the sensitive
reserved words and characters. That has been pretty effective as well. Perhaps combining both approaches
would be a pretty effective solution.
Hans:
For the short term solution: I would add some code to the spUpdateTable procedure, checking the parameter
for all the words that are allowed (or: not allowed!).
If it is always an UPDATE statement (as the name of the procedure suggests), then the SELECT word is
not allowed in the parameter string, and hence the code exists without any action.
Martin:
I understand some of what you said but the examples went over my head.
maybe a binary view like this:
do this but not this
with the explaination shwing how the dynamic or actual code becomes risky.
Editor Response:
Martin, I provided a couple C# examples yesterday of what you could. So, for the binary view you have requested,
here is how it could be done in C# in a way that exposes the database to SQL Injection.
WARNING…THE FOLLOWING IS AN EXAMPLE OF HOW TO WRITE CODE ALLOWING SQL INJECTION…DO NOT WRITE YOUR CODE LIKE THIS
Examples of Values retrieved from controls in the user interface containing SQL Injection. The Injected code is in Red.
String Notes = "A Whole Bunch of text where injection code can be entered'
;SELECT * FROM Master.DBO.Sysdatabases;"
int NoteID = 23;
Inline SQL build as a single string without parameters
string CommandText = "Update SomeTable Notes = '{0}' WHERE NoteID = {1} ";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(String.Format(CommandText, Notes, NoteID));
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
The SQL Statements resulting from this C# snippet would be
Update SomeTable Notes = ‘A Whole Bunch of text where injection code can be entered’;
Select * from Master.DBO.Sysdatabases;
@Notes WHERE NoteID = 23
Executing this SQL code in the database will do three bad things
- It will update the Notes column for every row in SomeTable with the text
- It will return a list of all the databases mounted on the server
- It blows up after doing the first two items because the third statement is not a valid SQL command
Unless the whole thing is wrapped in a transaction, error trapping is in place, and the transaction is rolled back, your database is now corrupted. A developer who doesn’t handle injection probably doesn’t have the skill or ambition
to handle errors either.
Do you have something to share about security? Send your comments to btaylor@sswug.org
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting SQL Server 2008 Indexes
In this article, Alexander Chigrik explains how you can troubleshoot SQL Server 2008 index problems.
Featured White Paper(s)
Web Content Management
The power of the World Wide Web has dramatically changed the way companies conduct business with their customers, partners, … (read more)