10

I was in a user group meeting yesterday and they pointed out that using parameterized queries is better than harcoding the query. That got me to thinking, does this do anything beneficial(obviously on a much bigger scale than this though):

DECLARE @Client1 UNIQUEIDENTIFIER,
@Client2 UNIQUEIDENTIFIER
SET @ClientId1 ='41234532-2342-3456-3456-123434543212';
SET @ClientId2 = '12323454-3432-3234-5334-265456787654';

SELECT ClientName
FROM dbo.tblclient
WHERE id IN (@Client1,@Client2)

As opposed to:

SELECT ClientName
FROM dbo.tblclient
WHERE id IN ('41234532-2342-3456-3456-123434543212','12323454-3432-3234-5334-265456787654')
mameesh
  • 3,651
  • 9
  • 37
  • 47
  • 2
    Can you clarify what is meant by better in the first sentence of the question. I'm familiar with the [exploits of Mom](http://xkcd.com/327/) but I'm somewhat suspicious of other claims. – Conrad Frix Aug 11 '11 at 19:43

3 Answers3

4

Parametrized queries and IN clause are actually not trivially implemented together if your IN list changes from time to time.

Read this SO question and answers: Parameterize an SQL IN clause

Parameters, by design, are one value only. Everything else other than that must be manually implemented, having in mind security issues, such as SQL Injection.

From a performance perspective, you will have better performance for parametrized queries, specially if a same query is ran repeatedly, but with different parameters values. However, if you have a dynamic IN list (sometimes 2 items, sometimes 3), you might not get the advantage of using parametrized queries.

Do not lose hope, though. Some folks have been able to implement that (Parametrized queries and IN clause). It's, again, not trivial, though.

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123
1

It shouldn't hurt, but you're going to get the most effect from prepared statements when you use queries that are generated by user input. If they're clicking a button to "show all", it's not a big deal; however, if you're prompting for a user to enter their name, you seriously need to parameterize the input before inserting/updating/selecting/etc.

For example, if I entered my name as "Mike DROP TABLE MASTER);" or whatever a big table name is in your DB, It could get really ugly for you. Better safe than sorry, right?

EDIT: OP commented here and asked a question. Updated with a code example.

public int myNum; 
SqlParameter spNum=new SqlParameter("@myNum", SqlDbType.Int); 
//you can also check for null here (but not really relevant in this case)
command.Parameters.Add(spNum); 
string sql="INSERT INTO Table(myNum)";
sql+=" VALUES(@myNum)";
command.CommandText = sql;
int resultsCt = command.ExecuteNonQuery();

See how the code is forcing the input to be an integer BEFORE it does any work with the database? That way if anybody tries any shenanigans it's rejected before it can do harm to the DB.

rownage
  • 2,392
  • 3
  • 22
  • 31
  • I think he's asking from a performance perspective more than a SQL Injection risk one. – JNK Aug 11 '11 at 19:35
  • Ah. If that's the case then I'll remove. Thanks for the clarification. – rownage Aug 11 '11 at 19:36
  • Up to the OP, honestly. Just what I got from the question. – JNK Aug 11 '11 at 19:37
  • So as for your last part. Your saying even with my query using the table variables it will allow this to happen with the SQL Injection if theres a user input? – mameesh Aug 11 '11 at 20:45
  • 1
    Yeah, most languages have proper functionality in place for you to protect against injections to at least some extent. I made an edit to my answer to show you a short example in C#. – rownage Aug 12 '11 at 12:33
1

On huge databases and complex queries with many joins the database can use time building an execute plan. When using parameterized queries the execute plan stays in the database cache for some time when calling the query multiple times with different parameters

Martin
  • 1,521
  • 3
  • 18
  • 35