38
votes
Accepted
Why does SQL Injection not happen on this query inside a stored procedure?
This code works properly because it is:
Parameterized, and
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 ...
- 67.7k
23
votes
Accepted
Duplicate row with Primary Key in PostgreSQL
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 ...
- 167k
19
votes
Accepted
How to Dynamically change the database using TSQL
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 ...
- 178k
18
votes
Accepted
How to drop all of my functions in PostgreSQL?
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 ...
- 167k
18
votes
Accepted
Trigger function taking column names as parameters to modify the row
Problem
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 ...
- 167k
16
votes
Switching between databases with dynamic SQL
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.
Example
use tempdb;
select db_name();...
- 3,503
16
votes
Accepted
How to use function parameters in dynamic SQL with EXECUTE?
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(
_username text
...
- 167k
15
votes
Accepted
what is causing arithmetic overflow in the query below?
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 ...
- 80.3k
15
votes
How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?
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 ...
- 167k
13
votes
Accepted
User-shared queries: Dynamic SQL vs. SQLCMD
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 ...
- 67.7k
13
votes
Accepted
Dynamic SQL query - how do I add an int to the code?
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 ...
- 37.4k
13
votes
How does use of sp_executesql with parameters protect against SQL injection?
double-up
To answer your question, you need to experiment with alternatives to using sp_executesql with parameters:
Using EXEC (without sp_executesql)
Using sp_executesql (without parameters)
Both ...
- 37.4k
12
votes
Accepted
Can sp_executesql be configured / used by default?
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("...
- 4,717
12
votes
How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?
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 ...
- 29.7k
12
votes
Accepted
EXEC vs SP_EXECUTESQL Performance
This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server).
Why use EXEC() some ...
- 178k
11
votes
Accepted
Switching between databases with dynamic SQL
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-...
- 67.7k
11
votes
Accepted
If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?
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 ...
- 16.2k
11
votes
Accepted
sp_executesql Not Working with Parameters
erland
You can't use object names as parameters in dynamic SQL. That's why your first attempt doesn't work
If you combine your first and second attempts, you can at least parameterize the @StoreId ...
- 37.4k
10
votes
Accepted
PostgreSQL trigger function that selects from %current% table
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 ...
- 54.5k
10
votes
Accepted
PostgreSQL GRANT ALL on mutliple tables using LIKE
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 ...
- 54.5k
10
votes
Copy indexes from one table to another
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:
select pg_get_indexdef(idx....
- 78.2k
9
votes
Wildcard search using parameters in function with dynamic SQL
Let's suppose for country_name you wanted to do a leading and trailing wildcard search.
You don't need dynamic SQL for this. Just:
CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text)
...
- 167k
9
votes
Accepted
Safe alternative to exec(sql)
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 (...
- 178k
9
votes
Accepted
Performance issue with sp_executesql and VARCHAR parameter
What you're running into is the curse of local variables.
The short of it is that when you declare a variable and then use it in a query, SQL can't sniff the value.
It sometimes uses magic numbers ...
- 37.4k
9
votes
Accepted
Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391
In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". ...
- 67.7k
8
votes
Accepted
Is it good practice to create tables dynamically?
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 ...
- 9,752
7
votes
Accepted
Prepared statement execution with variable number of parameters to be bound
At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary ...
- 22.4k
7
votes
Accepted
Dynamically convert hstore keys into columns for an unknown set of keys
This can be done, very efficiently, too. Not in a single statement, though, since SQL demands to know the return type at call time. So you need two steps. The solution involves a number of advanced ...
- 167k
7
votes
Accepted
How to run ALTER TABLE on a list of tables sequentially in a function
The problem is that you cannot use parameters as object names (tables, columns, etc.) in straight SQL statements, like your ALTER TABLE above.
I use 'straight' here as an opposite of 'dynamic' - ...
- 30.3k
7
votes
Accepted
Assignment of a column with dynamic column name
The error is that the INTO clause is not part of the SQL command. It's part of the plpgsql command EXECUTE.
And dynamic field names are currently not possible, neither in SQL nor PL/pgSQL. But there ...
- 167k
Only top scored, non community-wiki answers of a minimum length are eligible