A More Elegant Way of Escaping Dynamic Sql

A more elegant way of escaping dynamic SQL?

Not sure how you execute your sql query, if you use sp_executesql, could be something like this

EXECUTE sp_executesql 
          N'SELECT * FROM YouTable WHERE job_code = @job_code',
          N'@job_code varchar(100)',
          @job_code = @job_code;

Escape Character in SQL Server

To escape ' you simly need to put another before: ''

As the second answer shows it's possible to escape single quote like this:

select 'it''s escaped'

result will be

it's escaped

If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).

e.g. instead of doing

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)

try this:

DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'

mssql elegant way for masking set of characters

The trick is to prepend the fixed pattern with the input, and then grab the right-most n characters:

RIGHT('xxxxxxxxxx' + 'abcd', 10)

How to escape simple SQL queries in C# for SqlServer

Since using SqlParameter is not an option, just replace ' with '' (that's two single quotes, not one double quote) in the string literals. That's it.

To would-be downvoters: re-read the first line of the question. "Use parameters" was my gut reaction also.

EDIT: yes, I know about SQL injection attacks. If you think this quoting is vulnerable to those, please provide a working counterexample. I think it's not.

Securely escaping dynamic table names in MySQL using Codeigniter

The escaping mechanisms are only for data strings, not for schema names. In other words, only for the content of a table, not for its structure. So you'll either have to paste the string into the query yourself, or avoid using tables in this way. The ? of query templates won't help you there.

If you paste the string into the table name, you can use the usual PHP string concatenation mechanisms. You should make extra sure that you check the string against a suitably strict regular expression, to avoid SQL injection. Make sure that you really only paste a single random string of the format you generate, and nothing else.

As an alternative, you could have one big table, containing all the selections, and use an additional column to hold your identification hash, or some other suitable key to identify a single selection. That way, you wouldn't have to modify the database schema during normal operations. I believe most developers, me included, would rather avoid such modifications by program code. Good database design works with a fixed schema.

How to drop SQL table from C# without opening to code injection?

You'll need to escape/encode the table name as an identifier. In SQL Server, this is typically done by replacing any closing brackets (]) in the name with two consecutive brackets (]]), and then surrounding the result with brackets ([...]).

You can either write a simple method to do this yourself, or take on a dependency like ScriptDom, which allows you to do this:

var escapedTableName = Identifier.EncodeIdentifier(tableName);

If your SQL Server database is set to use double-quoted identifiers, do something similar, but with double-quotes instead of brackets. Or, with ScriptDom:

var escapedTableName = Identifier.EncodeIdentifier(tableName, QuoteType.DoubleQuote);

Note that encoding the table name makes any .s act as if they're part of the table name itself. If your table name is supposed to have qualifiers (database, schema), each of those must be escaped individually, so you'll probably want to have them passed in as a separate argument, or create a separate type to represent the combination of these names.

is there a more elegant way to re-use code blocks?

You can use Subquery Factoring (aka Common Table Expressions, aka CTEs) to make a query with multiple subqueries easier to read. E.g. your query would become:

WITH params1 AS (SELECT to_date('02.AUG.2018 00:00:01', 'DD.MON.YYYY HH24:MI:SS') sys_date -- for testing, sysdate can be overwritten
                 --sysdate sys_date 
                 FROM   dual),
     params2 AS (SELECT extract(YEAR FROM sys_date) current_year,
                        sys_date
                 FROM   params1),
     params3 AS (SELECT to_date('01.APR.' || current_year || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_this_year,
                        to_date('01.APR.' || (current_year - 1) || ' 00:00:00', 'DD.MON.YYYY HH24:MI:SS') first_of_april_last_year,
                        sys_date
                 FROM   params2)
SELECT CASE
         WHEN sys_date <= first_of_april_this_year THEN
          first_of_april_last_year
         ELSE
          first_of_april_this_year
       END previous_first_of_april
FROM   params3;

As an aside, working out the 1st of April based on a date can be done more simply:

SELECT add_months(TRUNC(add_months(SYSDATE, -3), 'yyyy'), 3)
FROM   dual;

Here, we go back 3 months from the specified date, truncate the resultant date to the year (to get to 1st Jan of that year) and then simply add 3 months back, to take us to 1st April of that year.

---

Related Topics