Real-life examples of SQL injection problems for SQL Server using only Replace As Warning?

Question

I know that dynamic SQL queries are bad due to problems with SQL injections (as well as performance and other problems). I also know that parameterized queries are preferred to avoid injection problems, we all know that.

But my client is still very stubborn and thinks that just

var UserName=Request.Form["UserName"];
   UserName=UserName.Replace("'","''");
   SQL="SELECT * FROM Users where UserName='" + UserName + "'";

Is protection against SQL injection sufficient (SQL Server (Only), not mysql)?

Can someone give me a real example of SQL Injection attack that can still go through the Replace case above? Guess if there are any problems with the Unicode character?

I need real, real-life examples of attacks that can still go through this simple replacement.

My question is for SQL Server only, and I know that MySQL has some problems with the \ character.

Answer 1

This will not work if you use NUMBER .

"SELECT * FROM data WHERE id = " + a_variable + ";"

using

1; DROP TABLE users

Gives you

SELECT * FROM DATA WHERE id=1;DROP TABLE users;

Take a look

• SQL injection
• MSDN SQL Injection

EDIT

Look at this. This is very close to your question.

Validate SQL Injection

Answer 2

Enter your age: 21; drop table users

SELECT * FROM table where age = 21; drop table users;

ouchies

Answer 3

. :

SQL=SQL.Replace("''","'");

SQL, , '' ' .

: :

SELECT * FROM tab WHERE col = '<input value goes here>'

, , :

SELECT * FROM tab WHERE col = ''

... SQL.Replace( "''", "'" ) :

SELECT * FROM tab WHERE col = '

, .

, SQL.Replace( "'", "' '" ), :

SELECT * FROM tab WHERE col = ''''

, col ( " , , " ). .

, - :

SQL = "SELECT * FROM tab WHERE col = '" & ParamValue.Replace("'", "''") & "'"

, , . , , , SQL stament.

, , , . MS SQL server QUOTED_IDENTIFIER , . , , , . , escape- ( ) !!

:

SET QUOTED_IDENTIFIER OFF
SELECT " "" '' "

:

" ''

, , , . , QUOTED_IDENTIFIER , . :

http://msdn.microsoft.com/en-us/library/ms174393.aspx

Answer 4

XKCD:

Answer 5

WHERE.

MSSQL * 2005), , id :

"SELECT * FROM data WHERE id = '" + a_variable + "';"

, (, EXECUTE, EXEC sp_executesql) WHERE , SQL-. 100% , , - , .

- , . . SO SQL- .

Answer 6

SO SQL-":

[...] SQL.

,

• Replace("'","''") ( SQL , . Roland w.r.t. QUOTED_IDENTIFIER),

• (.. -) ,

• datetime datetime (.. , , SQL Server).

, SQL- SQL Server.

Unicode, , MySQL. ( ). , , , SQL .

Answer 7

.

SQL = SQL.Replace("'","''");

.

, , , .