Setting MaxValue for Migrations

12 03 2013

I’ve just been using the MigratorDotNet framework for the first time. I was creating a migration that would create a new table with various new fields. One of the fields I wanted to create was to store files so therefore I wanted a varbinary(max) field. If you leave the size undeclared, it will create a varbinary(8000). The way I got around this was to create my column as follows:

Column file = new Column("BinaryFile", System.Data.DbType.Binary, int.MaxValue, ColumnProperty.NotNull);

Using the int.MaxValue gives the same result as varbinary(max). However, this doesn’t create a varbinary(max) field (at least not in SQL Server 2008 R2 which is what I am using). It creates an image field instead. This isn’t necessarily a problem, it still gives me the space I require. Importantly though, Microsoft are now advising you do not use this type in new development as it will be removed in a future version of SQL Server (see MSDN article here). We’ve weighed this up and have decided that this is not a problem for us at this time because we are developing improvements to a current system with no plans for any SQL upgrade in the future. There are also uses of the image type in other parts of the database. We have instead logged it in our technical backlog to go through and change all uses of this type at a later date. Personally, in a new project, I would not use it, just in case!

I did some searching in the hope I would find a more elegant solution (something akin to the EF Code First data annotations would have been nice) but I didn’t find anything. If anybody comes across this post, and you’ve found a nicer way to do this, please comment on here to let me know!





Casting SqlParameter/Object as a Byte array

2 03 2010

I’ve just spent ages working this out, and all the time I was just making a silly mistake! So hopefully this may save someone else the pain! For anyone that doesn’t want to read the rest of the post, the code to do it is:

byte[] fileContents = (byte[])sqlParameters[3].Value;

I was saving a PDF file as a byte array and then inserting that to my database as a varbinary. I needed to be able to then get that varbinary back out of the database as a byte array, and open it without corrupting the information.

So casting is the obvious candidate. But I made a silly mistake and had the following:

byte[] fileContents = (byte)sqlParamters[3].Value;

Which caused a problem as I was trying to cast a single byte to a byte array. I then got all caught up in using the ToString() method on the parameter value and then converting it from a string to a byte array. It just kept getting corrupted!

When I actually sat back and thought about it, a SqlParameter is just an object. So a simple cast is all that’s required. When I took a step back, I realised my code should have been:

byte[] fileContents = (byte[])sqlParameters[3].Value;

See what I mean about it being a stupid mistake?! All I needed was the square brackets in the casting and now it works perfectly, giving me back my lovely PDF, uncorrupted!

Sometime problems aren’t as complicated as you first think and the easy solution is the right one 🙂





SQL Injection & Cross-Site Scripting (XSS)

23 06 2009

Today saw the need for me to test a current web application I’m working on for SQL Injection and Cross-Site Scripting. Although I was aware of both, I’d never had to test for them before. So in order to test for them, I had to learn how to do them first. I’m going to keep this post basic, as I found quite a few very useful pages & articles which I will link to at the end.

SQL Injection:

When creating or updating a database table, in my web application I am going through a Data Access Layer (DAL). I always use paramaterised queries as a rule, as I was aware that this was important security wise. To test SQL injection capabilities on my web application, I created a new table in my database called TestTable. Then I went to a web form that ultimately runs the UPDATE command on my database. In the ‘Description’ text fields, I inserted the following text
'; DROP TABLE TestTable --'
Which could result in the SQL code being
UPDATE NameTable
SET Name = 'Me', Description = ''; DROP TABLE TestTable --'
WHERE Id='1'

Obviously very dangerous code! Luckily, due to my DAL and parameterised queries, it took the text as literal text and not executable text and updated the database as expected, but with
'; DROP TABLE TestTable --'
as the description for the name ‘Me’. As I said, this is a basic explanation, there are also tests you should do on a login. I’m using a customised version of the .Net Membership API on my web application, in which case it was already accounted for. However, if you have written you’re own membership authentication, you should definitely test it for SQL injection.

The links I found useful for SQL injection as promised are:
http://msdn.microsoft.com/en-us/library/ms998271.aspx
http://www.sitepoint.com/article/sql-injection-attacks-safe/

Cross-Site Scripting (XSS):

If you are using Response.Write in your ASP.net pages, you are definitely vulnerable to XSS, also if you are using query strings. Even if you’re not, I would still recommend testing for any vulnerabilities. In order to test, I used some simple Javasript to pop up an alert box. I then pasted this on to the end of my url, i.e. “www.mywebsite.co.uk/login.aspx?’insert javascript here'” and pressing enter. If you get a pop up box with the text you gave to your Javascript alert command, then that’s bad! ASP.net handled my error with HttpRequestValidationException and didn’t carry out any of the Javascript, this is good! I tried the same thing when logged into my website, and also entered the Javascript in a text box on a form and submitted it. Again .Net handled it for me using a HttpRequestValidationException. All is well.

The links I found useful for Cross-site scripting as promised are:
http://en.wikipedia.org/wiki/Cross-site_scripting
http://www.sitepoint.com/article/cross-site-scripting/

Conclusion:

I’m pretty happy with how my tests went. I am however going to add some extra validation checks on my text boxes anyway, make sure all UPDATE, INSERT and DELETE commands are carried out via stored procedure and also make sure my user for connection to the database can only execute required stored procedures and select statements (that are parameterised) and cannot do any direct table access.

As I mentioned at the beginning of the post, I am new to the in’s and out’s of SQL Injection and XSS, so my understanding on some points may be a little off! If anyone who reads this thinks I’m wrong in something I’ve said, or can add anything to anything I’ve said, please comment because I would like to learn more about this subject.