Entity Framework: Is Contains() (aka ‘Like’) case insensitive for SQL Server queries?

Just a note to self.

The Contains method on String when used in an Entity Framework “where” clause is case insensitive (if the server is set to ignore case).

Example:

Assume the “NoteContent” column on the table “Notes” is a varchar column. If I run the following Linq query against the database where db is a DbContext then I get the same result every time.

var num = db.Notes.Where(n => n.NoteContent.Contains(“NoTe”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“NOTE”)).Count();

var num = db.Notes.Where(n => n.NoteContent.Contains(“note”)).Count();

All produce the same value for “num”.

I’m making this note to self because I keep forgetting and because I keep seeing people do this:

var someString = “whatever”;

var num = db.Notes.Where(n => n.NoteContent.ToUpper().Contains(someString.ToUpper())).Count();

You will need to do this if the underlying SQL Server has been set to be case sensitive.

Thanks to the people who added comments, correcting my original post that said it didn’t matter.

Entity Framework Generates Weird SQL

If you ever see Entity Framework code that looks something like this:

SELECT
1 AS [C1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4]
FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
WHERE 1 = 0

and your EF LINQ query was nothing like it (and who’s would be?), then you probably have a

somelist.Contains(x.Value)

in your query and the collection “somelist” is empty.

I hit this today and was scratching my head until I found this:  

LINQ to Entity Framework submits meaningless query to SQL Server for Contains(empty array)

Hope that helps someone.

 

Entity Framework 4 – The selected stored procedure returns no columns (part 2)

My original post on this topic, Entity Framework 4 – The selected stored procedure returns no columns, has been getting quite a lot of hits recently and I realize that of all the solutions I gave on that page the one I use the most is the one that’s mentioned way down the page in a comment.  So I thought it would be a good idea to highlight it.

The simplest solution to this issue is based on some data given by Frank Lan, a guy from MS Online Community Support on the SilverLight forums.  He said:

I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click “Get Column Information”, you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there’s column information or not. But I suggest you trying to execute your query in this way to see if there’s any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.

That suggested a simple way to handle any situation where you have applied the solutions I gave in the original article (don’t just ignore those solutions and do this easy one) or where you are forced to use temp tables because temp variables won’t do and you still can’t get the columns:

Stick an “if” statement at the top of your sproc that checks if all parameters are null and if true returns the structure.

For Example:
if @param1 is null and @param2 is null then
begin
select
cast(null as varchar(10)) as Column1,
cast(null as bit) as Column2,
cast(null as decimal) as Column3
end

Just be aware that the casts are required otherwise EF won’t be able to tell what the data types are supposed to be.  Even if you return values instead of nulls you still need the casts.

Hope that helps.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)

Well, that’s a mouthful, isn’t it?

I saw this in our production web site’s log and wondered how serious an issue it is.  Our site is ASP.NET MVC 3, using Entity Framework 4.1 for database connectivity.

I dug around and found the best explanations here:

• Michael Aspengren blog post with the same loooong title
• Adapter client throws an exception on performing an operation after the connectivity is restored between the adapter client and the SQL Server database
• Troubleshooting: Connection Forcibly Closed

It’s only appears once in the current log and not at all in the last archived log, so I’m not going to worry about it, yet.  But I thought other people might be interested in what it is, what causes it and what to do about it and I wanted to record the explanation sources in case it happens again and I need to do something about it.

Entity Framework 4 – The selected stored procedure returns no columns

(2011-11-04: I noticed a lot of activity on this post and I realized that the solution I use most is in one of the comments way down the page.  So I have written an additional article here, Entity Framework 4 – The selected stored procedure returns no columns (part 2), to highlight it.  I suggest you do read the solutions given below because they may apply and could help improve your stored procedure but if you are in a hurry the part 2 article gives you a fast fix.)

You create a stored procedure, you bring it into your entity model, you start to create a function import, you click the “Get Column Information” button and you get “The selected stored procedure returns no columns.”

The reasons I’ve seen for this are:

1. You are returning your result set using dynamic sql, so EF can’t figure out at design time what the shape of the results will be.
2. You are selecting from a temp table and EF can’t figure out the shape of the results at design time.
3. Your stored procedure crosses databases and although you have all the rights you need in the database containing the sproc, you don’t have the necessary rights to the database objects in the other database.  E.g., you don’t have select rights on a table in the other database.

The solutions I’ve found are:

For #1

1. Alter your sproc so the result set structure can be figured out at design time. E.g., put the results into a table variable and select from that.
2. Create a dummy sproc with the same name and signature as the final sproc that contains a select statement which creates a result set with the desired structure. (E.g., select cast(1 as int), cast(“a” as varchar(20)) … etc.) Refresh your entity model and add the function import – EF will see the structure and you can create the Entity to receive the results. Now replace the dummy sproc in the database with your final version.

For #2

1. Use a table variable instead of a temp table

For #3

1. Give the user that is calling the sproc the correct rights in the other database or databases.

I hope people find that useful.

(2010-12-19:  See my reply to minkbear’s comment below for another possible solution.)

The Danger of Implicit Data Type Conversions in SQL Server

I have never before hit the problem that I discovered today because, call me crazy but, I always store dates in a datetime column and not in varchar columns and I rarely use implicit conversions. However, today one of my coworkers was moving some data from an old table that was all varchar columns into a new table that actually defined the column using the data type of the data that was to go into the column (imagine that).

He started getting the date “1900-01-01 00:00:00 000” appearing in the new table and couldn’t figure out why. He asked me to help, so we looked at default constraints- no, triggers – no. Then I had him look at the source data and we saw a lot of empty values in the result of the select query. Then we realized that it wasn’t saying “null” in the result but nothing at all. So we added a “where len(rtrim(ddate)) = 0″ and sure enough the dates were either empty strings or contained spaces and SQL Server was converting them to ‘1900-01-01 00:00:00 000” (the earliest date of the smalldatetime data type – another WTF because the column was a datetime not a smalldatetime).

At first this made no sense to me. The documentation clearly says “When you convert to date and time data types, SQL Server rejects all values it cannot recognize as dates or times.“.  So I decided to play around and I think I figured it out.

SQL Server will implicitly convert a binary value to a datetime.  So if you give it an empty string (and this is my guess) it tries to convert it and fails, so then it tries to use the empty string as a binary and it succeeds, because the empty string has a binary value of zero and that equates to the date “1900-01-01 00:00:00 000”.  

If you give it spaces, SQL Server will trim the spaces and then attempt to do the conversion, so again, it ends up as an empty string.  (Try inserting ‘   1/1/2000  ‘ and you’ll see that SQL Server will convert it correctly despite the spaces, therefore I assume it trims them before attempting the conversion.)

Just try these nonsensical implicit conversions:
create table #tempTest ( Id int null, dtColumn datetime null )
go

insert into #tempTest (Id, dtColumn)
values (1, '')
go

insert into #tempTest (Id, dtColumn)
values (2, 0x0)
go

insert into #tempTest (Id, dtColumn)
values (3, 11)
go

select * from #tempTest
go

select cast(dtColumn as binary) as dtColumnAsBinary from #tempTest
go
Results:

Id          dtColumn
----------- -----------------------
1           1900-01-01 00:00:00.000
2           1900-01-01 00:00:00.000
3           1900-01-12 00:00:00.000

dtColumnAsBinary
--------------------------------------------------------------
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000000000000
0x000000000000000000000000000000000000000000000000000B00000000

So next time you consider just letting SQL Server implicitly convert your data – think again. You really have no idea what is going to come out the other end.

As a final note, I’ve been using SQL Server since version 1.1 (I guess that gives away my age) and I never hit problems like this before. I think it’s because I rarely use implicit conversions when I’m moving data around and I set a column’s data type to be the type of the data that will be going into it. It’s interesting how these simple practices have insulated me from problems like this for twenty years.

References:

Full list of implicit and explicit conversions supported by SQL Server (scroll down a bit)

All about Data Type Conversion in SQL Server

Thank you Julie Lerman

I have a few reasons to thank Julie Lerman.  First her excellent book on Entity Framework (Programming Entity Framework) saved me all sorts of pain and suffering when I recently did a project using EF for data access.  EF is a great tool and will be even better in the future but the first release can be a bit unintuitive at times and especially a pain in the butt when dealing with certain kinds of stored procedures.  Her book saved me from wasting time, wasting resources and the  frustration of going down blind alleys.

Today she saved me again.  I tried to change the namespace of the data access layer of my project and after a couple of minutes of find and replace I could see I was wandering into a minefield.  So I learned first hand that you have to come up with a good namespace right at the beginning because changing it after you’ve got an established EF model makes a trip to the dentist look like fun.

So I right-clicked the folder and selected “Revert”.  And of course that solved everything … not!  Next time I tried to run the app I got this helpful message: “Schema specified is not valid. Errors: Models.XXXX.csdl(3,4) : error 0019: The EntityContainer name must be unique. An EntityContainer with the name ‘XXXX’ is already defined.”

I tried to debug it myself for a while, but no luck so I turned to Bing and found that other people posting to various forums had hit a similar problem with no real solutions provide. Then I found a page on Julie’s site (Entity Framework Error 0019) that solved the whole thing.

So, thanks Julie, and keep writing the great books!

DB Class – Pattern or Anti-Pattern?

An alternative title for this article could be “When is a Data Access Layer not a Data Access Layer?”

In the last couple of years I’ve come across a “pattern” that is used for database access that I think deserves the title of anti-pattern.  I’m going to call it the “DB Class” pattern because the examples of it I’ve come across have used the name “DB”, “DBCommon” and similar to name the class.

The way it works is that the class has methods that are passed the name of a stored procedure or a sql statement and an array of parameters.  The methods generally return data tables or scalar values or data sets.

Here is an example of a call to such a method:

DataTable customers = DB.GetTable("dbo.GetCustomers", params);

Where “dbo.GetCustomers” is the name of a stored procedure and “params” is a SQLParameter array.

What’s wrong with this pattern is that the calling code has to know the structure of the database – name of stored procedure, names and types of the parameters.  A true Data Access Layer hides the database from the other parts of the application.

A better example of a call to such a method would be:

DataTable customers = DB.GetCustomers(nameStartsWith, custType);

Where the method parameters are standard .NET data types, not SQLParameters.

An even better example would be:

List<Customer> customers = DB.GetCustomers(nameStartsWith, custType);

Where a collection of objects is returned.  This would be using a Transaction Script pattern.

I’ve seen this “DB Class pattern” used in two ways:

1. The methods are called directly from all over the place.  For example, in the code-behind of an aspx page or in an event handler of a Windows Form.
2. The methods are called by a “data access layer”.  For example, in the code-behind a call is made to a method in a data access class, then the data access class makes the call to DB.

The 1st way exposes the database structure to the harsh light of day – not a good idea.  Try to make some database changes or change the parameters of a stored procedure and you will be doing text searches and endless regression testing to make sure you didn’t break something.

The 2nd way is not as bad, at least the stored procedure and the parameters are hidden from the UI code but these methods return generic DataTable objects and then the code that made the call has to know the names of the table columns, which brings us back to the same issue as before – code outside the data access layer has to know the database structure.

If you really have to use DataSets and DataTables for passing your data around then use Typed DataSets.  Visual Studio has designers and wizards that make it easy and they also create a “TableAdapter” class that encapsulates your database interactions.

Better still is to use a Domain Model pattern and an ORM tool that converts data from a database into objects and collections of objects.  NHibernate, LLBLGen and now Entity Framework are just three of the many choices available.

So, to conclude, I think this “DB Class” is an anti-pattern, and if no one else has named it as such then I claim that distinction and all the honors antendent to it 🙂

More on Anti-Patterns:

• What’s an Anti-Pattern?
• Anti-Pattern
• List of Anti-Patterns (I think “Blame Storming” and “Alcohol Fueled Development” are two of my favorites, at least name-wise.  I took the liberty of adding my anti-pattern to the list.)

SQL Injection Attacks

I’ve never had to deal with a SQL Injection attack before, mainly because in all the sites I’ve ever created I pass any input from the site to the database in parametrized form.  I have never built SQL statements on the fly by adding user supplied values in strings.

But this week a new client  received such an attack which brought down the corporate web site.  So I had to get involved and figure out what to do.

The intention of the attack was to insert a call to some JavaScript on a remote server into the html of the site, but all they managed to do was make every page fail.  They would have succeeded if it were not for the ineptitude of the web site builder.  He had the content of each page in a table with an integer id, but he used a varchar as the data type of the id.  The text that the attacker injected into all varchar fields made the ids invalid and the select statement trying to get the page failed and return a null. (What do you call that?  Double Ineptity?)

When I looked into the database I found all character fields were filled with “”.  (BTW, if you Google that string you will find it all over the place, so the incompetent programmer who created my client’s site is not alone.)

The attacker managed to inject the script because:
1. The guy who created the site put inline sql all over the place.  Here is a sample:

sql = "select * from content where id = '" & Request.QueryString("id") & "'"

 

2. He never checked any user input or any query string for dangerous content.
3. When he set up the database he gave admin privileges to the database userid the web site used.

That was his side of the story. The attackers side went like this.  They used the query string to inject a huge sql statement that they hid in a hex encoded number.

page.aspx?id=29;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x44 ... 200%20AS%20NVARCHAR(4000));EXEC(@S);--

I cut out the full number because it was almost 2k digits long. This huge string of hex digits was cast into a very clever set of sql statements that opened a cursor on the sysobjects and syscolumns tables and updated every character type column by adding that script tag to it.

One clever thing about this is that all of the articles I read when I was researching what to do mentioned looking out for strings such as “select”, “drop”, “insert” and “update” but not one mentioned “declare”, “set” or “cast”.  Of course catching the “;” would have prevented the attack and most articles did mention that.

So, how were the attacks handled?

I created a function that handled the single quote issue and nuked the naughty words(like “declare”, “select”, etc.).  Then me and two other developers went through the 500+ locations in the code where the !@#%$^ who created the site had built his sql statements by concatenating strings with variables and we wrapped every variable and every Request.QueryString(“paramname”) in a call to the function.  Tedious in the extreme, but necessary.

And it worked.  I know that because they have tried the attack several times since (robots never give up) and it has not succeeded.  So when you do a Google search for that script tag you won’t find my client’s web site in the results.

So, how do you avoid SQL Injection Attacks?

• Never build sql statements by concatenating strings and variables.
• Use parameters (such as SQLParameter or OLEDBParameter or whatever the equivalent is in your language of choice) in your database calls.
• Validate user input and URL query strings so you detect and reject potentially dangerous entries.
• Limit how much a user can enter in text boxes so they can’t add on a bunch of sql statements.
• If you are stuck with an existing site that is wide open to attack, then wrap every variable and every use of the URL query string in a call to a sanitizing method.
• Get educated on SQL Injection Attacks.  Read the resources below and anything else you need to until you get the idea.

Some resources on SQL Injection Attacks:

• SQL Injection Attacks – Are you safe?
• SQL Injection Attacks By Example
• SQL Injection Attacks and Some Tips on How to Prevent Them
• SQL Injection
• SQL Injection and Cross-Site Scripting

One Size Fits All (not)

I recently re-read SQL Server 2005 Bible, a great book that covers everything you ever wanted to know about SQL Server but were afraid to ask.  At one point in the book the author says that your data abstraction layer should be in your database in the form of stored procedures and he gives some good arguments as to why.

Right after that I read Pro LINQ Object Relational Mapping in C# 2008, another excellent book that shows you how to create an ORM using LINQ (both LINQ to SQL and Entity Framework) .  When introducing the fact that you can use stored procedures in LINQ, the author very definitely says that even though they are supported, you shouldn’t use them and he gives some good arguments as to why.

Two diametrically opposing views in two really good books.  The error both authors make is thinking that one size fits all.  The truth is that “all” can vary widely so one size just ain’t gonna cut it.

My view is that everything we have in our industry is a tool and each tool has a range of uses.

Example: I recently joined a project that heavily uses stored procedures in line with the “SQL Server Bible” author.  I think it is appropriate given the huge amount of data the application has to deal with.  Tuning many queries within an inch of their life, including being able to completely rewrite them or split a single query into several, is very necessary on this project.  Using LINQ or some other ORM tool to only generate the SQL and never use stored procedures would just not work. 

Another Example: I was working on a project a couple years ago where LINQ to SQL would have been perfect for fast creation of a Data Access Layer in an existing application that needed some major enhancements, but unfortunately we had to still support users with Windows 2000 machines and .NET Framework 3.5 doesn’t support W2K, so we were stuck with using typed datasets.  But we didn’t use stored procedures for our abstraction layer.  It really wasn’t necessary.  Yes, some tuning was needed, but the app usage was mild and the amount of data relatively small, even after many years of use, so an ORM would have been great and would have made our lives simpler.

Anyway, the moral of the story is use the tool that is appropriate and remember that absolutes are unobtainable.