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),
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 ...
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 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 ...
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 ...
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
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
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 ...
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" ...
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) ...
To drop all functions (including aggregates) in a given schema (be careful with this!):
Postgres 11 or later
Procedures have been added.
The system catalog pg_proc slightly changed: prokind replaces proisagg and proiswindow - and also tags functions and the new procedures
SELECT INTO _sql
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();';
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 ...
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 ...
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-...
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 ...
I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:
EXECUTE format('SELECT colname FROM %I', TG_RELID)
EXECUTE format('SELECT colname FROM %I.%I', ...
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 ...
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 ...
This query on the system catalog creates the necessary DDL script:
SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
WHERE nspname LIKE 'ceu_shard_test_merge_%';
Note the use of format() to escape identifiers if necessary.
format() requires PostgreSQL 9.1+.
Replace with quote_ident() in older versions.
Either way, that's totally possible, given that all your parameters are of the same data type.
EXECUTE ... USING happily takes an array, which is treated as a single argument. Access elements with array subscripts.
create or replace function test_function(_filter1 text = null
, _filter2 text = null
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 ...
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 ...
This sounds prime for a front end display solution. Query 1 would pull back your data, Query 2 would pull back the column names and in code when you build what ever structure you use to display you set the headers from the second query.
While a Pure SQL Method may be possible it will be dynamic SQL and code maintnence would be a nightmare.
Also your ...
The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.
CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
SELECT columns, 'TABLE2' as tablename
I am curious to know how the column names getting concatenated.
Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.
The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.
From SET @local_variable (Transact-SQL)
Do not use ...