How to create dynamic parameters in SQL Server stored procedure
-
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.
-
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.
-
Yes, I showed an example of this already
-
God dammit abarker, I should have checked it but I was too busy playing in onepost
-
No worries. Discourse encourages that sort of posting.
-
@pjh show us what he said!
-
Undeleted, for your convenience. No need for @PJH to use his admin powers, lofty though they be.
-
I'll sick @ben_lubar on you next time you deem fit to delete something.
-
I'll sick @ben_lubar on you next time you deem fit to delete something.
Do your worst.
-
This post is deleted!
-
(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)
Bwahahaha!
-
(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)
Summoning @ben_lubar!
-
Everyone should enable edit history.
There, can I leave now?
-
Ritual for unsummoning
-
Ritual for unsummoning
I have temporarily allowed viewed of my revision history. Beware what lurks in a deleted post.
-
This post is deleted!
-
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]
-
No need for @PJH to use his admin powers, lofty though they be.
You jest, Shirley...
-
I don't jest. And stop calling me Shirley.
-
-
Doh!
Corrected.
-
-
Except it wasn't a ninja edit. Just an edit.
-
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
-
Damn, i was hoping for one-box infinicursion.
Interesting how different methods of writing the URL result in different one-boxings....
-
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.
-
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?
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.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.
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
-
So is Ruby, and people write forums in it.
It must have taken great strength of character not to put quotation marks around forums.
-
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.
-
<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 utilizingsp_executesql
.sp_executesql
is still in the master database in SQL Server.@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 usessp_excutesql
, which does not make it part of .NET.
@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 usingQUOTENAME()
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 usingQUOTENAME()
. In that case, you have other problems.
-
Can someone give both of these guys pedantic dickweed badges already?
-
I'm sure it's being considered. Very pedantically considered.
-
Make sure to flag it if you feel it is appropriate.
-
sp_executesql is still in the master database in SQL Server.
No disagreement there.
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?
The author has fallen into the trap of believing that because .NET uses sp_executesql for ad-hoc queries, the parameters are sanitized.
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."
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).
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 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.
-
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 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.
-
We were talking about funny numbers, letters, and spaces. If your db allows that it's already a special case.
-
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.
-
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.
-
I nominated you and @abarker.
I hereby accept this nomination, though I doubt that anything will come of it.
-
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.
-
Or you can go 'lalalalala FUCK YOU PEOPLE WHO DID THIS lalalala'
-
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.)
-
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"1. 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 (1):
1: for some value of app, like an Excel spreadsheet application... blech...
-
Solution: Oracle.
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
'' 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.
-
'' = null
leaves me with a cheap, dirty feeling.But that's not true. Neither is
'' = ''
, for that matter.
-
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.
-
Wait what, so you can't actually have an empty string?
What if my middle name is ''?
-
('' = '') ≝ false