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),
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 ...
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 ...
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();';
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
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
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" ...
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 ...
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) ...
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;
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-...
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 ...
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 ...
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 ...
GRANT doesn't take wildcards in table identifiers.
You can use ON ALL TABLES IN SCHEMA, but that requires a single schema name.
If you want to do things with wildcard pattern table names you will need to use PL/PgSQL's EXECUTE format(...) in a DO block to loop over the information_schema.tables view. See many related answers here on DBA.se and Stack ...
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 ...
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
If you don't always pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL:
CREATE OR REPLACE FUNCTION func(_a int = NULL
, _b text = NULL
, _c text = NULL)
RETURNS TABLE(...) AS
SELECT ... FROM ...
You can retrieve the complete DDL statement for each index from the Postgres system catalogs.
The following will retrieve all CREATE index statements for a single table:
from pg_index ind
join pg_class idx on idx.oid = ind.indexrelid
join pg_class tbl on tbl.oid = ind.indrelid
left join pg_namespace ns on ns.oid = ...
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 ...