This code works properly because it is:
Not doing any Dynamic SQL
In order for SQL Injection to work, you have to build a query string (which you are not doing) and not translate single apostrophes (') into escaped-apostrophes ('') (those are escaped via the input parameters).
In your attempt to pass in a "compromised" value, the 'Male; ...
Back in 2007, I asked for an easy way to generate a CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g. dbo.whatcha:
CREATE TABLE dbo.whatcha
id INT IDENTITY(1,1),
As others have said this is known as a PIVOT. There are several ways in which you can transform your data from rows into columns of data.
If you know the values ahead of time, then you can hard-code the values. Prior to the PIVOT function you would use an aggregate function with a CASE statement.
sum(case when [...
Ok, couple of things.
always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).
always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.
always use ...
Simple with hstore
If you have the additional module hstore installed (instructions in link below), there is a surprisingly simple way to replace the value(s) of individual field(s) without knowing anything about other columns:
Basic example: duplicate the row with id = 2 but replace 2 with 3:
INSERT INTO people
SELECT (p #= hstore('id', '3')).* FROM ...
I am struggling to find the reason of the arithmetic overflow. why is it happening?
Most likely the metadata is returning some unexpected values that your code cannot handle. For example:
-- Example values returned from sysfiles and FILEPROPERTY
@size integer = 1,
@spaceused integer = 10000;
-- The essence of the code in the question
Just to get these out of the way:
Technically speaking, both of these options are "dynamic" / ad hoc queries that are not parsed / validated until they are submitted. And both are susceptible to SQL Injection since they are not parameterized (though with the SQLCMD scripts, if you are passing in a variable from a CMD script then you do have an opportunity ...
You had to pick the spot where all possible complications come together.
SQL (or PL/pgSQL) does not allow to parameterize identifiers. You need dynamic SQL with EXECUTE for that.
But the special plpgsql variable NEW in trigger functions is not visible inside dynamic code executed with EXECUTE.
And it's further complicated by passing column names as ...
George's answer does solve your problem, but it leaves you wide open to SQL injection attacks.
While converting an INT to a VARCHAR 11 is likely not going to cause any issues, sysname is the equivalent of an NVARCHAR 128, and you can jam a lot of extra code in there.
To make your code totally safe, you'd want to do this:
DECLARE @stringsvar NVARCHAR(1000) ...
Sure, there is a way - there's always a way...
If you declare variable and store in it the database and the procedure to run, you can exec it, with parameters.
declare @db sysname = 'master.sys.sp_executesql';
exec @db N'select db_name()';
set @db = 'msdb.sys.sp_executesql';
exec @db N'select db_name()';
It is ...
Simple case, static SQL
The non-dynamic solution with crosstab() for the simple case:
SELECT * FROM crosstab(
'SELECT b.x, f.name, f.x * b.x AS prod
FROM foo f, bar b
ORDER BY 1, 2'
) AS ct (x int, "A" int, "B" int, "C" int, "D" int, "E" int
, "F" int, "G" int, "H" int, "I" int, "J" int);
I order resulting columns by foo....
You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.
CREATE OR REPLACE FUNCTION insert_records_for_notification(
, _state text
, _district text
, _bloodgroup text
, _status text
, _approverejectstatus text
, _emailsubject text
The PRINT statement inside the WHILE loop does execute in the order you expect, but the output is buffered before sys.sp_executesql returns. Implementation details mean the buffered output is reversed.
Using RAISERROR (@cmd, 0, 1) WITH NOWAIT; instead of PRINT forces the buffer to flush after each call, giving you the results in the order you expect. IIRC ...
I think you meant to use sp_executesql:
EXEC sp_Executesql @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOut = @TableNotExists OUTPUT
And as JonSeigel pointed out in the comment, you have mis-spelled a parameter in your statement:
SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE =...
To drop all functions (including aggregates) in a given schema (be careful with this!):
For Postgres 11:
- Procedures were added
- The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures):
SELECT INTO _sql
string_agg(format('DROP %s %...
The reason the SQL statements are getting wrapped with sp_executesql is the setting of the SqlCommand.Commandtype property and passing any Parameters to the command.
SqlCommand cmd = new SqlCommand("proc1", con);
cmd.CommandType = CommandType.StoredProcedure;
What's the best we can do in PostgreSQL without a "column definition
list" to generate that kind of presentation?
If you frame this as a presentation problem, you might consider a post-query presentation feature.
Newer versions of psql (9.6) come with \crosstabview, showing a result in crosstab representation without SQL support (since SQL can't produce ...
As Martin Smith said, you need to pivot the data, whether with an explicit PIVOT as referenced or something like this (SQL Fiddle):
, SUM(Case When Month='Jan' Then Amount Else 0 End) MonthJan
, SUM(Case When Month='Apr' Then Amount Else 0 End) MonthApr
FROM Result GROUP BY SiteCode;
If you just need results from multiple schemas, you can re-use the same query string and set the search_path in between:
SET search_path = u111, public;
SELECT * FROM foo;
SET search_path = u222, public;
SELECT * FROM foo;
The schema search path search_path in Postgres works much like the search path a file system. Related:
How does the search_path ...
Are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of them?
You need a real stored procedure (CREATE PROCEDURE) to benefit from temporary table caching. This includes temporary stored procedures (#procname).
Point #3 on this blog post ...
Session-level changes made in a sub-process (i.e. EXEC / sp_executesql) go away when that sub-process ends. This covers USE and SET statements as well as any local temporary tables created in that sub-process. Creation of global temporary tables will survive the sub-process, and so will modifications made to local temporary tables that exist prior to the sub-...
For certain types of parameterization, it's not going to matter if you use EXEC() or sp_executesql, because some things can't be parameterized anyway. For example, you expressed in the comments (please update your question to be more specific about your requirements!) that you are parameterizing table names, but these can't be parameterized because they need ...
This will require really having fun with escaping and re-escaping single quotes, but I think the technique you're after is:
@DB NVARCHAR(255) = QUOTENAME(N'dbname'),
SET @SQL = N'EXEC ' + @DB + '.sys.sp_executesql '
+ 'N''CREATE VIEW dbo.vWhatever
SELECT x = 1, y = ''''x'''', z = GETDATE();''';
SSMS WILL NOT, I REPEAT, WILL NOT SWITCH TO THE CONTEXT OF A USE COMMAND YOU RUN IN DYNAMIC SQL.
If the ultimate goal is to execute some other dynamic SQL inside the chosen database, this is easy enough:
DECLARE @db sysname = N'db1';
DECLARE @exec nvarchar(max) = QUOTENAME(@db) + N'.sys.sp_executesql',
@sql nvarchar(max) = N'SELECT DB_NAME();';
There isn't any definitive answer for this problem.
You've correctly identified some of the possible tradeoffs (single table=simpler/possibly slower/more contention?, multiple tables=more complicated/faster, maybe?/harder to use in other contexts?).
The general advice is to start with the more straight-forward single-table solution, then break them out if ...
From an answer by Remus Rusanu:
Dynamic SQL runs in the same session as the calling code. The issue is
not session, but scope. Your dynamic SQL creates the temporary tables
inside the sp_executesql call and thus the created #temp table is only
visible within that sp_executesql call, as documented in MSDN:
You can validate that you are using the ...
You can do something along the following lines:
test=> CREATE OR REPLACE FUNCTION temptabl(cnt integer)
RETURNS SETOF integer AS
CREATE TEMPORARY TABLE tmp_container ON COMMIT DROP AS
FROM generate_series(1, cnt) t(a);
IF (SELECT count(1) FROM tmp_container) > 5
RETURN QUERY SELECT a FROM ...
There is nothing wrong with using dynamic SQL if you must. In fact in some circumstances it is the only option that you have. It is more of a recommendation not to use it as yes it can lead to a SQL injection if your input is not sanitized, and yes using dynamic SQL in modules that get called often can be detrimental to it's performance.
I don't think ...
Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it.
Since you will be ...