Give me parameterized SQL, or give me death

I have fairly strong feelings when it comes to the stored procedures versus dynamic SQL argument, but one thing is clear: you should never, ever use concatenated SQL strings in your applications. Give me parameterized SQL, or give me death. There are two good reasons you should never do this.

First, consider this naive concatenated SQL:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';

Code like this opens your app to SQL injection attacks, and it’s a huge, gaping vulnerability. Steve Friedl’s SQL Injection Attacks by Example provides an excellent visual blow-by-blow of what can happen when you write code this naive. Here’s the Reader’s Digest version:

SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x' OR full_name LIKE '%Bob%';

I know what you’re thinking. No, escaping the strings doesn’t protect you; see Steve’s article.

Second, parameterized SQL performs better. A lot better. Consider the parameterized version of the above:

SqlConnection conn = new SqlConnection(_connectionString);
conn.Open();
string s = "SELECT email, passwd, login_id, full_name " +
"FROM members WHERE email = @email";
SqlCommand cmd = new SqlCommand(s);
cmd.Parameters.Add("@email", email);
SqlDataReader reader = cmd.ExecuteReader();

This code offers the following pure performance benefits:

  • Fewer string concatenations
  • No need to worry about any kind of manual string escaping
  • A more generic query form is presented to db, so it’s likely already hashed and stored as a pre-compiled execution plan
  • Smaller strings are sent across the wire

Non-parameterized SQL is the GoTo statement of database programming. Don’t do it, and make sure your coworkers don’t either.

Related posts

There is no longer any such thing as Computer Security

There is no longer any such thing as Computer Security

Remember “cybersecurity”? Mysterious hooded computer guys doing mysterious hooded computer guy... things! Who knows what kind of naughty digital mischief they might be up to? Unfortunately, we now live in a world where this kind of digital mischief is literally rewriting the world’s history. For proof of that, you

By Jeff Atwood ·
Comments
Hacker, Hack Thyself

Hacker, Hack Thyself

We’ve read so many sad stories about communities that were fatally compromised or destroyed due to security exploits. We took that lesson to heart when we founded the Discourse project; we endeavor to build open source software that is secure and safe for communities by default, even if there

By Jeff Atwood ·
Comments
Let’s Encrypt Everything

Let’s Encrypt Everything

I’ll admit I was late to the HTTPS party. But post Snowden, and particularly after the result of the last election here in the US, it’s clear that everything on the web should be encrypted by default. Why? 1. You have an inalienable right to privacy, both in

By Jeff Atwood ·
Comments
Welcome to The Internet of Compromised Things

Welcome to The Internet of Compromised Things

This post is a bit of a public service announcement, so I’ll get right to the point: Every time you use WiFi, ask yourself: could I be connecting to the Internet through a compromised router with malware? It’s becoming more and more common to see malware installed not

By Jeff Atwood ·
Comments

Recent Posts

Stay Gold, America

Stay Gold, America

We are at an unprecedented point in American history, and I'm concerned we may lose sight of the American Dream.

By Jeff Atwood ·
Comments
The Great Filter Comes For Us All

The Great Filter Comes For Us All

With a 13 billion year head start on evolution, why haven’t any other forms of life in the universe contacted us by now? (Arrival is a fantastic movie. Watch it, but don’t stop there – read the Story of Your Life novella it was based on for so much

By Jeff Atwood ·
Comments
I Fight For The Users

I Fight For The Users

If you haven’t been able to keep up with my blistering pace of one blog post per year, I don’t blame you. There’s a lot going on right now. It’s a busy time. But let’s pause and take a moment to celebrate that Elon Musk

By Jeff Atwood ·
Comments
The 2030 Self-Driving Car Bet

The 2030 Self-Driving Car Bet

It’s my honor to announce that John Carmack and I have initiated a friendly bet of $10,000* to the 501(c)(3) charity of the winner’s choice: By January 1st, 2030, completely autonomous self-driving cars meeting SAE J3016 level 5 will be commercially available for passenger use

By Jeff Atwood ·
Comments