SQL Injections with Replace Single-Quotation and Validate Integers

SQL Injections with replace single-quotation and validate integers

If this is a legacy project that is coded this way then, whilst not optimal, I'm not currently aware of any way that it can be susceptible to SQL injection as long as every string is treated in that manner and the queries are just simple ones as you have shown.

I cannot state any more certainty than that however. Without using parametrised queries there is always the possibility that there is some vulnerability that you have not yet considered.

Manually escaping the quotes yourself is error prone and can sometimes fail in ways that are difficult to anticipate in advance. For example with the following table

CREATE TABLE myTable(title VARCHAR(100))
INSERT INTO myTable VALUES('Foo')

And stored procedure using dynamic SQL built up with string concatenation

CREATE PROC UpdateMyTable
@newtitle NVARCHAR(100)
AS
/*
Double up any single quotes
*/
SET @newtitle = REPLACE(@newtitle, '''','''''')

DECLARE @UpdateStatement VARCHAR(MAX)

SET @UpdateStatement = 'UPDATE myTable SET title='''  + @newtitle + ''''

EXEC(@UpdateStatement)

You can try the following

Normal update

EXEC UpdateMyTable N'Foo'
SELECT * FROM myTable /*Returns "Foo"*/

SQL Injection attempt foiled

EXEC UpdateMyTable N''';DROP TABLE myTable--'
SELECT * FROM myTable  /*Returns "';DROP TABLE myTable--"*/

SQL Injection attempt succeeds and drops the table

EXEC UpdateMyTable N'ʼ;DROP TABLE myTable--'
SELECT * FROM myTable  /*Returns "Invalid object name 'myTable'."*/

The issue here is that the third query passes U+02BC instead of the standard apostrophe and then the string is assigned to a varchar(max) after the sanitation occurs which silently converts this to a regular apostrophe.

Until I read the answer here that issue would never have occurred to me.

Replace single quotes in SQL Server

You need to double up your single quotes as follows:

REPLACE(@strip, '''', '')

SQL injection single quote Vulnerability

You should use parameterized command instead. Using string.Replace is just a bad idea.

var command = conn.CreateCommand();
command.CommandText = @"SELECT *
        FROM users
        WHERE us_username = @user
        AND us_password = @password";
cmd.Parameters.Add("@user", txtUser.Text);
cmd.Parameters.Add("@password", txtPassword.Text);

This might be a potential candidate for your setup :

As an example, note the following trivial Stored Procedure:
create procedure GetData ( @param varchar(20) ) as
begin
declare @s varchar(200)
select @s = 'select * from dataTable where name = ''' + @param + ''''
exec (@s)
end

This SP may be called from a Web page, which executes validation code
before passing the input to the SP. At a minimum, this validation code
either verifies that the input does not contain a quote, or sanitizes
it to double any existing quote. For instance, the validation code may
be using string.Contains(), string.Replace(), Regular expressions,
etc. It is also possible that this Web page is behind a finely-tuned
Web Application Firewall that validates all input and verifies that no
quotes are included. A malicious user or attacker can submit malicious
code containing a modifier letter apostrophe (U+02BC, URL encoded to
%CA%BC). This will easily pass applicative validation code and WAF
filters, since these search for an actual quote (U+0027) which does
not exist in the input at this time. Obviously, IDS/IPS systems would
also not detect anything amiss. The validation mechanisms may even
search for various encodings of a quote, such as URL Encoding, UTF-8
encoding, Hex encoding, double encoding, and more – however, U+02BC is
none of these, and is in fact a completely different character value.

---

And this is where the interesting (or scary) part starts – the Unicode
homoglyph translation is not limited to base alphabet characters...
Specifically, the Unicode character U+02BC (modifier letter
apostrophe) can be translated by the database server to a simple quote
– ' (U+0027). There are, of course, many other similar examples.

Source : http://web.archive.org/web/20130401091931/http://www.comsecglobal.com/FrameWork/Upload/SQL_Smuggling.pdf

Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?

First of all, it's just bad practice. Input validation is always necessary, but it's also always iffy.

Worse yet, blacklist validation is always problematic, it's much better to explicitly and strictly define what values/formats you accept. Admittedly, this is not always possible - but to some extent it must always be done.

Some research papers on the subject:

• http://www.imperva.com/docs/WP_SQL_Injection_Protection_LK.pdf
• http://www.it-docs.net/ddata/4954.pdf (Disclosure, this last one was mine ;) )
• https://www.owasp.org/images/d/d4/OWASP_IL_2007_SQL_Smuggling.pdf (based on the previous paper, which is no longer available)

Point is, any blacklist you do (and too-permissive whitelists) can be bypassed. The last link to my paper shows situations where even quote escaping can be bypassed.

Even if these situations do not apply to you, it's still a bad idea. Moreover, unless your app is trivially small, you're going to have to deal with maintenance, and maybe a certain amount of governance: how do you ensure that its done right, everywhere all the time?

The proper way to do it:

• Whitelist validation: type, length, format or accepted values
• If you want to blacklist, go right ahead. Quote escaping is good, but within context of the other mitigations.
• Use Command and Parameter objects, to preparse and validate
• Call parameterized queries only.
• Better yet, use Stored Procedures exclusively.
• Avoid using dynamic SQL, and dont use string concatenation to build queries.
• If using SPs, you can also limit permissions in the database to executing the needed SPs only, and not access tables directly.
• you can also easily verify that the entire codebase only accesses the DB through SPs...

How can sanitation that escapes single quotes be defeated by SQL injection in SQL Server?

There are a few cases where this escape function will fail. The most obvious is when a single quote isn't used:

string table= "\"" + table.Replace("'", "''") + "\""
string var= "`" + var.Replace("'", "''") + "`"
string index= " " + index.Replace("'", "''") + " "
string query = "select * from `"+table+"` where name=\""+var+"\" or id="+index

In this case, you can "break out" using a double-quote, a back-tick. In the last case there is nothing to "break out" of, so you can just write 1 union select password from users-- or whatever sql payload the attacker desires.

The next condition where this escape function will fail is if a sub-string is taken after the string is escaped (and yes I have found vulnerabilities like this in the wild):

string userPassword= userPassword.Replace("'", "''")
string userName= userInput.Replace("'", "''")
userName = substr(userName,0,10)
string query = "select * from users where name='"+userName+"' and password='"+userPassword+"'";

In this case a username of abcdefgji' will be turned into abcdefgji'' by the escape function and then turned back into abcdefgji' by taking the sub-string. This can be exploited by setting the password value to any sql statement, in this case or 1=1-- would be interpreted as sql and the username would be interpreted as abcdefgji'' and password=. The resulting query is as follows:

select * from users where name='abcdefgji'' and password=' or 1=1-- 

T-SQL and other advanced sql injection techniques where already mentioned. Advanced SQL Injection In SQL Server Applications is a great paper and you should read it if you haven't already.

The final issue is unicode attacks. This class of vulnerabilities arises because the escape function is not aware of multi-byte encoding, and this can be used by an attacker to "consume" the escape character. Prepending an "N" to the string will not help, as this doesn't affect the value of multi-byte chars later in the string. However, this type of attack is very uncommon because the database must be configured to accept GBK unicode strings (and I'm not sure that MS-SQL can do this).

Second-Order code injection is still possible, this attack pattern is created by trusting attacker-controlled data sources. Escaping is used to represent control characters as their character literal. If the developer forgets to escape a value obtained from a select and then uses this value in another query then bam the attacker will have a character literal single quote at their disposal.

Test everything, trust nothing.

SQL injection: isn't replace(', '') good enough?

No, it is not enough. It will do in a pinch, but it is a very weak alternative, and using parameterized queries or parameterized stored procedures is better, if your platform and/or RDBMS support either feature.

From

OWASP's SQL Injection Prevention Cheat Sheet

...this methodology is frail compared to using parameterized queries.
This technique should only be used, with caution, to retrofit legacy
code in a cost effective way.

There are more below

SQL injection — but why isn't escape quotes safe anymore?

Sql Injection Myths and Fallacies

SQL Injection after removing all single-quotes and dash-characters

How do I escape a single quote in SQL Server?

Single quotes are escaped by doubling them up, just as you've shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008:

DECLARE @my_table TABLE (
    [value] VARCHAR(200)
)

INSERT INTO @my_table VALUES ('hi, my name''s tim.')

SELECT * FROM @my_table

Results

value
==================
hi, my name's tim.

Insert text with single quotes in PostgreSQL

String literals

Escaping single quotes ' by doubling them up → '' is the standard way and works of course:

'user's log'     -- incorrect syntax (unbalanced quote)
'user''s log'

Plain single quotes (ASCII / UTF-8 code 39), mind you, not backticks `, which have no special purpose in Postgres (unlike certain other RDBMS) and not double-quotes ", used for identifiers.

In old versions or if you still run with standard_conforming_strings = off or, generally, if you prepend your string with E to declare Posix escape string syntax, you can also escape with the backslash \:

E'user\'s log'

Backslash itself is escaped with another backslash. But that's generally not preferable.

If you have to deal with many single quotes or multiple layers of escaping, you can avoid quoting hell in PostgreSQL with dollar-quoted strings:

'escape '' with '''''
$$escape ' with ''$$

To further avoid confusion among dollar-quotes, add a unique token to each pair:

$token$escape ' with ''$token$

Which can be nested any number of levels:

$token2$Inner string: $token1$escape ' with ''$token1$ is nested$token2$

Pay attention if the $ character should have special meaning in your client software. You may have to escape it in addition. This is not the case with standard PostgreSQL clients like psql or pgAdmin.

That is all very useful for writing PL/pgSQL functions or ad-hoc SQL commands. It cannot alleviate the need to use prepared statements or some other method to safeguard against SQL injection in your application when user input is possible, though. @Craig's answer has more on that. More details:

• SQL injection in Postgres functions vs prepared queries

Values inside Postgres

When dealing with values inside the database, there are a couple of useful functions to quote strings properly:

• quote_literal() or quote_nullable() - the latter outputs the unquoted string NULL for null input.
  
  There is also quote_ident() to double-quote strings where needed to get valid SQL identifiers.
• format() with the format specifier %L is equivalent to quote_nullable().
  
  Like: format('%L', string_var)
• concat() or concat_ws() are typically no good for this purpose as those do not escape nested single quotes and backslashes.

Escape single quote in sql query c#

Just replace single quote (') by double single quote like ''.

so your function will look like:

public SqlDataReader getpartyID(string partyName)
            {
                string query = "EXEC partyIDtoInsert'" +partyName.Replace("'", "''") + "'";
                return new DataAccessLayer().executeQuerys(query);
             }

I hope it will solve your problem. :)

---

Related Topics