Skip to main content
17 votes
Accepted

Should we still be using QUOTENAME to protect from injection attacks?

Yes, things haven't changed much in this area, you should be using quotename for any SQL server object names that are used in dynamic SQL (especially if they are supplied externally to your code). As ...
Martin Smith's user avatar
  • 84.9k
15 votes
Accepted

What function quotes an identifier in dynamic-sql with SQL Server?

The function that you're looking for is QUOTENAME! Through the practical use of square bracket technology, you can safely encapsulate strings to aid in the prevention of hot SQL injection attacks. ...
Erik Darling's user avatar
  • 41.8k
15 votes
Accepted

Is this SQL Procedure "injection proof"?

SQL injection requires that the malicious content be added to (injected into) an SQL query string, which is then parsed by the database engine and executed. As a result, anything that looks like a ...
mustaccio's user avatar
  • 26.2k
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 ...
Erik Darling's user avatar
  • 41.8k
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 ...
Erik Darling's user avatar
  • 41.8k
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) ...
Erwin Brandstetter's user avatar
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 (...
Aaron Bertrand's user avatar
8 votes

How does use of sp_executesql with parameters protect against SQL injection?

SQL injection is the act of taking the data of parameters that are used to create a query, and injecting them into the query. In other words, instead of using parameters orderid = @oid you are using ...
Charlieface's user avatar
  • 13.3k
8 votes

Is this SQL Procedure "injection proof"?

It's not enough that just the stored procedure is injection-proof. The code that calls the sproc must be secure too. As a trivial counterexample, consider the following C# code that calls your sproc: ...
vonPryz's user avatar
  • 3,081
7 votes
Accepted

Why most of Sql Injection need url like this "index.asp?id=123"

When you see URL's that have that kind of syntax id=123, there is a good chance the underlying code will use 123 as input to some kind of dynamic SQL statement. Unless the underlying code is written ...
Scott Hodgin - Retired's user avatar
6 votes

SQL Injection penn testing from the queries only

Parameters are passed as an RPC (remote procedure call) to SQL Server over the TDS protocol. Consequently, a parameterized query will show as RPC starting/completed events in a trace (SQL Trace or ...
Dan Guzman's user avatar
  • 28.3k
6 votes

Dynamic SQL query - how do I add an int to the code?

The issue is down to data type precedence. In this case, the INT data type is of higher precedence, as such the VARCHAR datatype is converted (or attempted to be converted) in this case to the data ...
George.Palacios's user avatar
5 votes
Accepted

Invoke-SqlCmd - does casting as integer prevent injection

You can, but there's problems with doing that. Primarily, it's still not a great idea because the pattern isn't extendable. It only works for certain data types. As soon as you have a string ...
Bacon Bits's user avatar
  • 1,895
5 votes
Accepted

Safely quoting type names to protect against SQL-injection

How would I quote the type name to protect against SQL Injection. You do not need format_type() for the purpose. You may have misunderstood Andrew there. All you need is the cast to regtype: SELECT ...
Erwin Brandstetter's user avatar
5 votes
Accepted

Does concating a string like this open me up to SQL injection?

No. That value will always be interpreted as a string and there's no way to put anything into the emaildomain column that could make the query execute unexpected code. It's either going to find ...
Aaron Bertrand's user avatar
5 votes
Accepted

How to pass parameters to sql script via psql \i command

Use \set to set a variable inside psql. Read about SQL interpolation in the manual here: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION Ideally, your script would read: ...
Erwin Brandstetter's user avatar
4 votes
Accepted

TSQL - Sql Injection

This is not a safe way to do it. You can run this quick demo to see what could go wrong create database demo Go use demo Go Create table SomeVeryImportantData (id int, txt varchar(30)); insert into ...
Dominique Boucher's user avatar
4 votes
Accepted

Divide by zero in ORDER BY CLAUSE

Oracle: order by 1/0 succeeds, because that clause alone is meaningless, the optimizer automatically eliminates it from the query at parse time and it never gets executed. SQL> select username from ...
Balazs Papp's user avatar
  • 40.8k
4 votes

How to make PostreSQL functions private (inaccessible to end users)?

Just use REVOKE to control the permissions Postgres assigns to roles. Documentation: The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the ...
Evan Carroll's user avatar
  • 63.7k
4 votes
Accepted

Is it efficient to use plpgsql functions only to validate identifiers and values?

Is this query safe from SQLi, when user_input_identifier and user_input_value are replaced by actual user input, which might be an SQLi attempt? The whole sql injection vulnerability is about ...
Evan Carroll's user avatar
  • 63.7k
4 votes

Safe alternative to exec(sql)

Take a look at using sp_executeSQL instead. It is fully paramaterized and so provides a much greater sense of safety to dynamic queries (and using alongside EXECUTE AS USER within procedures with ...
Nic's user avatar
  • 4,023
4 votes

Is this SQL Procedure "injection proof"?

With dynamic SQL, you first build the SQL statements and then execute them in a later step. But in this case, you are passing parameters to your query. When you execute this statement "EXEC test @...
RobMartin's user avatar
  • 149
3 votes

Dangerous MySQL functions

You are looking in a bad way. Those security limits can get back to bite you. The right solution is to sanitize user input etc. to prevent injection to happen. SQL Injection isn't an error on the ...
Aroly7's user avatar
  • 141
3 votes

How can I insert smiley faces into MySQL ( 😊 )

Do the Following things: Set the database charset to utf8mb4 Set the charset of column to utf8mb4 like below query: ALTER TABLE `comments` CHANGE `text` `text` VARCHAR(255) CHARACTER SET utf8mb4 ...
Poonam Gupta's user avatar
3 votes

PostgreSQL. Is using random tag in dollar quoting without escaping input safe?

Postgres doesn't cache ad-hoc SQL query plans; only if your host environment is using prepare will the query plan be cached. But if prepare is used, then parameterisation is also being used so dollar ...
Colin 't Hart's user avatar
3 votes

Is this SQL Procedure "injection proof"?

The procedure itself is fine, it would only be vulnerable if you were doing some kind of sql generation and execution inside it. You are however correct that you are doing the attack incorrectly, ...
Jason Goemaat's user avatar
2 votes

Stop a person from entering my database and make changes

So first you need check the access log on your web server. If it's SQL injection you should see probably straight URLs. You can also restore database once again, and turn on log queries, then you can ...
Marek Skiba's user avatar
2 votes

PostgreSQL. Is using random tag in dollar quoting without escaping input safe?

UUIDs are pretty much guaranteed unique, so if you use a quality random UUID it's pretty-much guaranteed safe. A time-based UUID would be very risky. So if your random generator is of cryptographic ...
Jasen's user avatar
  • 3,593
2 votes
Accepted

Closing bracket ']' in LIKE wildcard

You have to escape the ]. That means before any character you want to literally search for, example % or _ or ], you have to add an escape character. For example \% to search for a literal % Example: ...
Yannick Liekens's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible