How to create dynamic parameters in SQL Server stored procedure

cartman82

quite right. If you build dynamic, parameterized SQL and pass it to sp_executesql, you get the same benefits as running a parameterized SQL query anywhere else, namely: prevention of SQL injection. Of course, this assumes that you are properly parameterizing your dynamic SQL instea

Thanks, that's what I figured. As long as you don't concatenate user input directly, you should be OK.

Matches

When building dynamic sql, you can dynamically add things like

and some_column = @value

safely, as long as you are passing @value as a PARAMETER and not straight string containing your where clause that takes user input.

Dynamic sql is to build filters properly based on user input, you still have to pass parameters properly.

One way I like to do it is to build the required insert columns from a datatable, and build the insert parameters from the columns in that datatable - then I do a sanity check using the target table schema (if it's an insert) saying 'While appending these datatable columns, make sure the column names map 1:1, if not, drop the insert column'

It's effective so far for my adhoc reporting tool that takes various known filters (and limited unknown ones) to create plethora of reports.

abarker

Yes, I showed an example of this already

Matches

God dammit abarker, I should have checked it but I was too busy playing in onepost

abarker

No worries. Discourse encourages that sort of posting.

Matches

@pjh show us what he said!

abarker

Undeleted, for your convenience. No need for @PJH to use his admin powers, lofty though they be.

Matches

I'll sick @ben_lubar on you next time you deem fit to delete something.

abarker

@Matches said:

I'll sick @ben_lubar on you next time you deem fit to delete something.

Do your worst.

abarker

This post is deleted!

abarker

@abarker said:

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

Bwahahaha!

Matches

@abarker said:

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)

Summoning @ben_lubar!

ben_lubar

Everyone should enable edit history.

There, can I leave now?

Matches

Ritual for unsummoning

abarker

@Matches said:

Ritual for unsummoning

I have temporarily allowed viewed of my revision history. Beware what lurks in a deleted post.

ben_lubar

This post is deleted!

abarker

@abarker said:

I have temporarily allowed viewed of my revision history. Beware what lurks in a deleted post.

For reasons, I have now revoked the ability for others to see my revisions. If you still wish to see the content of that deleted post continue into the spoiler.

Post 57 originally read: [spoiler]This post will be deleted[/spoiler]

PJH

No need for @PJH to use his admin powers, lofty though they be.

You jest, Shirley...

abarker

I don't jest. And stop calling me Shirley.

chubertdev

@abarker said:

I don't just. And stop calling me Shirley.

You don't just jest?

abarker

Doh!

Corrected.

chubertdev

@abarker said:

Doh!

Corrected.

@PJH, isn't there a badge for catching ninja edits?

abarker

Except it wasn't a ninja edit. Just an edit.

darkmatter

@chubertdev said:

isn't there a badge for catching ninja edits?

AFAIK it was suggested but not done.

http://what.thedailywtf.com/t/how-to-create-dynamic-parameters-in-sql-server-storedprocedure/1955/74

http://what.thedailywtf.com/t/how-to-create-dynamic-parameters-in-sql-server-storedprocedue/1955/74

http://what.thedailywtf.com/t/how-to-create-dynamic-parameters-in-sql-server-stored-procedure/1955/74

darkmatter

Damn, i was hoping for one-box infinicursion.
Interesting how different methods of writing the URL result in different one-boxings....

lucas

I've mainly done CMS dev for years so I tend to use a data mapper approach. Mainly because it is simple and I understand it e.g I've been working with Sitecore for the past 3 years, so I use the Glass Mapper project.

I been using Eloquent on PHP, because most of the time I am using Laravel. I normally use a Micro-ORM for whatever platform I am developing on. I almost never find myself dealing with data large enough to have to worry about it.

But I am one of those "shitty" web developers.

Groaner

@abarker said:

Bzzzt! .NET does no such thing. Go on, google it. I'll wait.

Why google it when we can verify it empirically? First, let's start with a class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Derp
{
    public class Class1
    {

        public static void Test(string connectionString)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand("select * from sys.databases where name = @database_name", conn))

                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@database_name", "master");

                    SqlDataAdapter sda = new SqlDataAdapter();

                    sda.SelectCommand = cmd;

                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                }
            }
        }
    }
}

Then, let's hook up a trace while we call Test (supplying it with a valid connection string, of course):

You were saying?

@abarker said:

Now, as google has told you, sp_executesql is a database engine stored procedure in SQL Server. Still a Microsoft product, but not .NET.

Not an untrue statement, but we just demonstrated that .NET (or, more specifically, the .NET SqlClient Data Provider) uses sp_executesql. Perhaps it's an implementation detail, but it is what it is.

@abarker said:

Still not quite right. If you build dynamic, parameterized SQL and pass it to sp_executesql, you get the same benefits as running a parameterized SQL query anywhere else, namely: prevention of SQL injection. Of course, this assumes that you are properly parameterizing your dynamic SQL instead of just dropping the parameter values into your SQL string.

I don't see how that differs substantially from what I said.

@abarker said:

You don't need QUOTENAME(). Just parameterize the SQL:

Yeah, there are much better ways to address that example than dynamic SQL. The cases where I end up using QUOTENAME() are a bit more sophisticated. If you know of a better way to do the following, I'd be very interested to hear it. Assume that t1 and t2 can have slightly different schema (but share a subset of columns), be in different databases, or on different servers.

create table t1
(
  id int identity(1,1),
  t_guid uniqueidentifier not null  
  constraint df_t1_t_guid default newsequentialid(),
  name varchar(100),
  address varchar(200),
  phone varchar(20),
  last_updated datetime,
  active bit,
  constraint pk_t1 primary key(id),
  constraint uq_t1_t_guid unique(t_guid)
)

create table t2
(
  id int identity(1,1),
  t_guid uniqueidentifier not null  
  constraint df_t2_t_guid default newsequentialid(),
  name varchar(100),
  address varchar(200),
  phone varchar(20),
  email varchar(256),
  last_updated datetime,
  constraint pk_t2 primary key(id),
  constraint uq_t2_t_guid unique(t_guid)
)

declare @insert nvarchar(max) = ''
declare @insertcols nvarchar(max) = ''

-- construct column lists based on common columns in each table
select @insertcols = @insertcols + quotename(c1.name) + ', ' 
from sys.columns c1
inner join sys.columns c2 on
(c1.name = c2.name)
where c1.object_id = object_id('t1')
and c2.object_id = object_id('t2')
and c1.is_identity = 0

-- trim trailing comma
set @insertcols = left(@insertcols, len(@insertcols) - 1)

-- construct insert statement with NOT EXISTS to only insert rows that have not yet been replicated
set @insert = 'insert into t2 (' + @insertcols + ')' + char(13) + 
  'select ' + @insertcols + ' from t1 
  where not exists 
  (
    select 1
    from t2
    where t2.t_guid = t1.t_guid
  )'

print @insert
exec sp_executesql @insert

Groaner

@blakeyrat said:

So is Ruby, and people write forums in it.

It must have taken great strength of character not to put quotation marks around forums.

Arantor

@Groaner said:

It must have taken great strength of character not to put quotation marks around forums.

Technically quotes would not be appropriate. Discourse, for all its faults, does provide a place where people can discuss things. It does it supremely badly but the evidence that you made a comment and that I can reply would seem to suggest that it does at least - technically speaking - fulfil the criteria of 'forums' especially given that topics can be categorised, thus offering multiple 'places' to discuss things.

abarker

@Groaner said:

<img src="/uploads/default/5158/67502e60d80f6fd5.png" width="690" height="300"> >
You were saying?

If you look carefully at that image, you'll see that it is in fact a SQL execute call, so this is a call being passed to your database by .Net SqlClient DataProvider. You have not demonstrated that sp_executesql resides in .NET, only that .NET is utilizing sp_executesql. sp_executesql is still in the master database in SQL Server.

@Groaner said:

@abarker said:
Now, as google has told you, sp_executesql is a database engine stored procedure in SQL Server. Still a Microsoft product, but not .NET.

Not an untrue statement, but we just demonstrated that .NET (or, more specifically, the .NET SqlClient Data Provider) uses sp_executesql. Perhaps it's an implementation detail, but it is what it is.

So what? Your original claim was that sp_executesql is part of .NET. I stated that it is not. So far, you have only showed that .NET uses sp_excutesql, which does not make it part of .NET.

@Groaner said:

@abarker said:
You don't need QUOTENAME(). Just parameterize the SQL:

Yeah, there are much better ways to address that example than dynamic SQL. The cases where I end up using QUOTENAME() are a bit more sophisticated.

Yes, I know dynamic SQL was unnecessary in the example I gave, I even said as much. I was just putting up a simple example to convey the point that - when actually building dynamic SQL - you can parameterize, and you don't need QUOTENAME().

Now, you were originally talking about using QUOTENAME() sanitize the tokens. Taken at its base meaning, this means that you are using QUOTENAME() on, well, everything. After looking at your post now, it appears that you should have been more specific in your original post.

Further, your example doesn't really have a need for QUOTENAME(). Now, I'm going to assume that you anonymized it and that your actual work does have a legitimate need for using QUOTENAME(). In that case, you have other problems.

antiquarian

Can someone give both of these guys pedantic dickweed badges already?

Onyx

I'm sure it's being considered. Very pedantically considered.

the_dragon

Make sure to flag it if you feel it is appropriate.

Groaner

@abarker said:

sp_executesql is still in the master database in SQL Server.

No disagreement there.

@abarker said:

So what? Your original claim was that sp_executesql is part of .NET. I stated that it is not.

Where did I say it was "part" of .NET? I only said that .NET "uses" it. Are Microsoft products under the GPL now?

@Groaner said:

The author has fallen into the trap of believing that because .NET uses sp_executesql for ad-hoc queries, the parameters are sanitized.

@abarker said:

Now, you were originally talking about using QUOTENAME() sanitize the tokens. Taken at its base meaning, this means that you are using QUOTENAME() on, well, everything. After looking at your post now, it appears that you should have been more specific in your original post.

Fair point. I was trying to choose a word that meant "things inserted into a string that are not parameters."

@abarker said:

Further, your example doesn't really have a need for QUOTENAME(). Now, I'm going to assume that you anonymized it and that your actual work does have a legitimate need for using QUOTENAME(). In that case, you have other problems.

I suppose not, if the system catalogs can be trusted, and there aren't any column names with spaces in them, that begin with numbers or are otherwise named mischievously (thankfully, there's no such treachery in these databases).

@antiquarian said:

Can someone give both of these guys pedantic dickweed badges already?

Which two guys were you talking about? I see several candidates for this esteemed distinction.

Matches

@Groaner said:

I suppose not, if the system catalogs can be trusted, and there aren't any column names with spaces in them, that begin with numbers or are otherwise named mischievously (thankfully, there's no such treachery in these databases).

If you're already building dynamic sql, you might as well add square brackets around your fields to prevent issues with spaces, numbers, funny letters, etc. in column names.

darkmatter

@Matches said:

If you're already building dynamic sql, you might as well add square brackets around your fields to ~~prevent issues with spaces, numbers, funny letters, etc. in column names.~~make your SQL no longer be ANSI-compliant and fail miserably when run against any (ok, most) non-SQLServer databases.

FTFY

Filed Under: Or does .NET have a layer to fix the bracketshit before you pass to the drivers for querying in the case of a non-SQLServer db?

antiquarian

@Groaner said:

antiquarian said:
Can someone give both of these guys pedantic dickweed badges already?

Which two guys were you talking about? I see several candidates for this esteemed distinction.

I nominated you and @abarker.

Matches

We were talking about funny numbers, letters, and spaces. If your db allows that it's already a special case.

abarker

@Groaner said:

Where did I say it was "part" of .NET? I only said that .NET "uses" it. Are Microsoft products under the GPL now?

It appears that I misremembered. I just reviewed your original post and you did indeed use the word "uses".

Now we have nothing more to argue over, it appears.

abarker

@Matches said:

If you're already building dynamic sql, you might as well add square brackets around your fields to prevent issues with spaces, numbers, funny letters, etc. in column names.

Well, that depends. If the environment you are working in has standards that are designed to prevent such issues, then you shouldn't need the brackets. And in fact, you might not want to use them as a sort of break in case someone does violate the standards.

abarker

@antiquarian said:

I nominated you and @abarker.

I hereby accept this nomination, though I doubt that anything will come of it.

darkmatter

@Matches said:

We were talking about funny numbers, letters, and spaces. If your db allows that it's already a special case

Or you could double-quote them. you know, like in the standards. That also works in SQL Server, but Microsoft would probably rather you didn't know because it'd make it easier to port your shit away from them.

Matches

Or you can go 'lalalalala FUCK YOU PEOPLE WHO DID THIS lalalala'

dkf

I think you'll find that you're better off going "lalalalala FUCK YOU PEOPLE WHO DID THIS lalalala" as that's standard-compliant. (Furthermore, anyone going [lalalalala FUCK YOU PEOPLE WHO DID THIS lalalala] is really annoying.)

M_Adams

@abarker said:

I usually output the SQL that gets run when I use dynamic SQL

Exactly! I make sure that the query is logged by the DBMS and the "app"¹. The DBMS logging is so that later I can point our off-site DBA's to it, allowing them to see what the exact query was (I'm nearly perfect, but sometimes my mileage does vary :) ) that brought the system to it's knees ( ooh... the data dictionary says the table has the indexes I need, but someone disabled them to optimize the table... sweet, table scans...)

Edit: forgot about the (¹):

¹: for some value of app, like an Excel spreadsheet application... blech...

M_Adams

@boomzilla said:

Solution: Oracle.

@Onyx said:

I clicked on the heart, but that's only because it's both the only option, and also because I don't think there's a name for what I'm feeling right now

@boomzilla said:

'' not being null would no doubt cause me a world of hurt

Same here... on all counts above...
'' = null leaves me with a cheap, dirty feeling.

ben_lubar

@M_Adams said:

'' = null leaves me with a cheap, dirty feeling.

But that's not true. Neither is '' = '', for that matter.

M_Adams

Oracle considers '' to be synonymous with null... so to be completely pedantic I should have written '' ≝ null for those who are unable to retain context while reading.

delfinom

Wait what, so you can't actually have an empty string?

What if my middle name is ''?

ben_lubar

('' = '') ≝ false