2.5 Ways Your ORM Is Vulnerable To SQL Injection

Published on: 2018-03-06

Someone recently told me that they don’t need to worry about SQL injection because they are using an ORM.

Oh boy.

ORMs don’t automatically prevent SQL injection

Object-relational mapping (ORM) tools allow developers to easily access an application’s data layer without having to write lots of redundant code.

 

Most ORMs will safely parameterize certain types of queries.  The following examples use Entity Framework and SQL Server, but these examples should apply to most other major ORMs and RDBMSs).

Our LINQ query making it easy to access our data layer:

A beautiful, clean, LINQ query

And then the SQL query that our ORM generated.

A query structure that only a mother could love

You’ll notice the generated SQL query is using sp_executesql that has parameterized our input variable value “TFly37”.  In this instance we can say the ORM did a good job following best practices in preventing a successful SQL injection attack.

But while ORMs may prevent some SQL injection attempts, there is no guarantee that they will prevent all injection attempts.

 

What follows are examples of when ORMs can allow for successful injection attacks.

Programatically assembled SQL

ORMs often provide the ability for developers to map the results of adhoc SQL queries to models.  Adhoc queries mean potential injection vulnerabilities.

Consider the following:

Looks a lot like your typical SQL injection vulnerability doesn’t it?

Entity Framework, or any ORM for that matter, won’t be able to recognize a parameter concatenated directly into the query string.

Now hopefully the developer has some really strong input validation  on the “username” parameter, but the fact still stands: this query is injectable and the ORM will happily execute it.

Stored Procedures

Does the logic needed in your app already exist as a stored procedure?

Excellent!  Let’s be DRY (don’t-repeat-yourself) and call our procedure directly from our ORM:

Parameterized with the value AND datatype – give this developer a raise!

So in this example, the ORM and developer have done everything right.  So where’s the injection vulnerability?

Uh oh…

Now let me be clear: this injection vulnerability is not the ORM’s fault.

However, it is the developer’s fault if he is blindly using a procedure without knowing what it does.  It’s also the DBA’s fault if she provided an injectable procedure assuming the developer would sanitize any input values in the app.

Security is hard.  Everyone needs to work together and be responsible for doing everything within their means to secure their applications.

ORM injection

Technically this isn’t an example of SQL injection.

But that’s why the title of this post says “2.5” instead of “3”.

In this example I’m using a dynamic LINQ to access my data:

Hello concatenation my old friend

If we pass the value \” OR 1 == 1 OR UserName==\” we will have the ORM convert it to the following query:

Game over

Injection takes many forms and doesn’t just come straight from SQL.  It’s important to follow best practices in all languages and tools to prevent security incidents.

Want to learn more?

If you are interested in learning more about how to protect yourself from SQL injection, be sure to  watch my online session at GroupBy at 9am Eastern on March 16th, 2018.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

4 Common Misconceptions About SQL Injection Attacks

Published on: 2017-11-07

Photo by Jaanus Jagomägi on Unsplash

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

SQL injection continues to be one of the biggest security risks that we face as database professionals.

Every year, millions of users’ personal information is leaked due to poorly written queries exploited by SQL injection.  The sad truth is that SQL injection is completely preventable with the right knowledge.

My goal today is to cover four common misconceptions that people have about SQL injection in an effort to dissuade any illusions that an injection attack is not something that can happen to you.

Prefer watching me get angry about these common misconceptions?  You can watch this content on my YouTube channel.

1. “My database information isn’t public”

Let’s see, without me knowing anything about your databases, I’m guessing you might have some tables with names like:

  • Users
  • Inventory
  • Products
  • Sales
  • etc…

Any of those sound familiar?

You might not be publicly publishing your database object names, but that doesn’t mean they aren’t easy to guess.

All a malicious user needs is a list of common database table names and they can iterate over the ones they are interested in until they find the ones that match in your system.

2. “But I obfuscate all of my table and column names!”

Oh jeez.  I hope you don’t do this.

Some people do this for job security (“since only I can understand my naming conventions, I’m guaranteeing myself a job!”) and that’s a terrible reason in and of itself.

Doing it for security reasons is just as horrible though.  Why?  Well, have you ever heard of some system tables like sys.objects and sys.columns?

SELECT 
	t.name, c.name 
FROM 
	sys.objects t
	INNER JOIN sys.columns c 
		on t.object_id = c.object_id

A hacker wanting to get into your system can easily write queries like the ones above, revealing your “secure” naming conventions.

Security through obscurity doesn’t work.  If you have table names that aren’t common, that’s perfectly fine, but don’t use that as your only form of prevention.

3. “Injection is the developer’s/dba’s/somebody else’s problem”

You’re exactly right.  SQL injection is a problem that should be tackled by the developer/dba/other person.

But it’s also a problem that benefits from multiple layers of security, meaning it’s your problem to solve as well.

Preventing sql injection is hard.

Developers should be validating, sanitizing, parameterizing etc…  DBAs should be parameterizing, sanitizing, restricting access, etc..

Multiple layers of security in the app and in the database are the only way to confidently prevent an injection attack.

4. “I’m too small of a fish in a big pond – no one would go out of their way to attack me”

So you run a niche business making and selling bespoke garden gnomes.

You only have a few dozen/hundred customers, so who would bother trying to steal your data with SQL injection?

Well, most SQL injection attacks can be completely automated with tools like sqlmap.  Someone might not care about your business enough to handcraft some SQL injection code, but that won’t stop them from stealing your handcrafted garden gnome customers’ data through automated means.

No app, big or small, is protected from the wrath of automated SQL injection tooling.

Interested in learning more about SQL injection attacks, including how to prevent them?  Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

ʼ;ŚℇℒℇℂƮ *: How Unicode Homoglyphs Can Thwart Your Database Security

Published on: 2017-09-12

Photo by Niketh Vellanki on Unsplash

For the past couple weeks I’ve been writing about how to protect your database from a SQL injection attack.  Today, we will keep the trend going by looking at how implicit unicode conversions can leave your data vulnerable.

You can also watch this content on my YouTube channel.

What’s a homoglyph?

A homoglyph is a character that looks like another character. l  (lowercase “L”) and 1  (the number) are considered homoglyphs.  So are O  (the letter) and 0   (the number).

Homoglpyhs can exist within a character set (like the Latin character set examples above) or they can exist between character sets.  For example, you may have the unicode apostrophe ʼ, which is a homoglyph to the Latin single quote character  .

How does SQL Server handle unicode homoglyphs?

Funny you should ask.  If you pass in a unicode character to a non-unicode datatype (like char), SQL implicitly converts the unicode character to its closest resembling non-unicode homoglyph.

To see this in action, we can use the unicode apostrophe from the example above:

SELECT 
	CAST(N'ʼ' AS nchar) AS UnicodeChar, 
	CAST(N'ʼ' AS char) AS NonUnicodeChar

You can see in the second column SQL automatically converted the apostrophe to a single quote:

Although this implicit character conversion can be convenient for when you want to display unicode characters in a non-unicode character set, it can spell disaster for your SQL Server security.

Unicode Homoglyph SQL Injection

If you are already using sp_executesql or QUOTENAME() when building your dynamic SQL queries then you are safe from this type of SQL injection.

I know you aren’t the kind of person who would ever write your own security functions when solid, safe, and tested functions like the above are available.  However, just this one time let’s pretend you think you can outsmart a hacker by writing your own quote delimiting code.

Using the same dataset as last week, let’s create a new stored procedure that is going to return some data from a user’s profile:

DROP PROCEDURE IF EXISTS dbo.GetProfile
GO
CREATE PROCEDURE dbo.GetProfile
	@Username nvarchar(100)
AS
BEGIN
	-- Add quotes to escape injection...or not?
	SET @Username = REPLACE(@Username, '''','''''')

	DECLARE @Query varchar(max)

	SET @Query = 'SELECT 
					FullName, 
					JoinDate
				FROM
					dbo.RegisteredUser
				WHERE
					UserName = ''' + @Username + '''
					'

	EXEC(@Query)
END
GO

Instead of using sp_executesql or QUOTENAME(), let’s try to write our own clever REPLACE() function that will replace single quotes with two sets of single quotes.  This should, in theory, prevent SQL injection.

If we test out a “normal” attempt at SQL injection, you’ll notice this logic works great.  Give yourself a pat on the back!

However, if we pass in a unicode apostrophe…:

The reason this happens is because we declared our @Query parameter as varchar instead of the unicode nvarchar.  When we build our dynamic SQL statement, SQL implicitly converts the nvarchar @Username parameter to the non-unicode varchar:

So if I replace apostrophes will that make me safe?

No.

I know it seems like black listing/replacing the unicode apostrophe would solve all of our problems.

And it would…in this scenario only.  There are more unicode homoglpyhs than just an apostrophe though.

Out of curiosity I wrote a script to search through the unicode character space to see what other homoglyphs exist:

DECLARE @FirstNumber INT=0;
-- number of possible characters in the unicode space
DECLARE @LastNumber INT=1114112;
 
WITH Numbers AS (
    SELECT @FirstNumber AS n
    UNION ALL
    SELECT n+1 FROM Numbers WHERE n+1<=@LastNumber
), UnicodeConversion AS (
SELECT
       n AS CharacterNumber,
       CASE CAST(NCHAR(n) as CHAR(1))
              WHEN '''' THEN NCHAR(n)
              WHEN ';' THEN NCHAR(n)
       END AS UnicodeCharacter,
       CAST(NCHAR(n) as CHAR(1)) AS ASCIICharacter
FROM Numbers
)
SELECT
       *
FROM
       UnicodeConversion
WHERE
       UnicodeCharacter IS NOT NULL
OPTION (MAXRECURSION 0)

Although the characters in the above screen shot might look similar, they are actually homoglyphs.

I decided to only search for single quotes and semi-colons since they are frequently used in SQL injection attacks, but this by no means is an extensive list of all of the characters you would want to blacklist.

Not only would it be very difficult to confidently blacklist every dangerous homoglyph, but new characters are being added to unicode all of the time so maintaining a black list would be a maintenance nightmare.  Especially if the person maintaining this code in the future isn’t familiar with these types of injection attacks.

And don’t be cheeky thinking you can filter out dangerous SQL keywords either – even if you REPLACE(@Username,’SELECT’,”), just remember someone can come by and pass in a value like ‘ŚεℒℇℂƮ’.

Conclusion

Don’t write your own security functions – they will fail.

Your best protection against SQL injection is to not use dynamic SQL.  If you have to use dynamic SQL, then use either sp_executesql and QUOTENAME().

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!