SQL injection prevention by replacing ' with ''

Question

I'm working on a web application which is vulnerable to SQL injection in its search box. It uses ASP.Net (C#) and Microsoft SQL Server.

In the search box it queries like:

Select Column1 from TBL where Column2 = N'  Here The Search box Content  ';

As you can see the above code is completely vulnerable to SQLi. What I did to mitigate this was just creating a method to replace all ' with '' which in SQL Server means it is a single quotation.

So if a user enters something like ' order by -- in the textbox the replace function will replace it with Replace("'","''"); and the SQL Server would never run the attacker's injected SQL.

So I just want to know that am I completely safe against SQLi? Or is there a way to bypass my injection protection?

No, not regex. Google it. Or read more about preventing SQLi in different languages [here](http://bobby-tables.com/about.html). (By the way, you do not specify what language the replace function is written in.) — Anders, Jul 06 '16 at 12:33
@Anders I update my question , I use c#. well is it important what language I use ?! I think replace exist in all languages. Are they diffrent ?! — Mohammad Sina Karvandi, Jul 06 '16 at 12:39
related: [How to defeat doubling up apostrophes to create SQLi attack?](https://security.stackexchange.com/questions/108472/) — tim, Jul 06 '16 at 12:43

S.L. Barth · Accepted Answer · 2016-07-06T12:43:44.230

No, that is not safe at all.

Replacing single-quotes with double-quotes or vice versa will only keep you safe from one type of injection. Also, what if the end user has a legitimate need to enter a single-quote mark?

As was pointed out in the comments, you should use Prepared Statements.

Below is an example, taken from the Microsoft documentation.

However, the technique is not unique to Microsoft. Every serious database supports prepared statements.

SqlCommand command = new SqlCommand(null, connection);

// Create and prepare an SQL statement.
command.CommandText =
    "INSERT INTO Region (RegionID, RegionDescription) " +
    "VALUES (@id, @desc)";
SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int, 0);
SqlParameter descParam = 
        new SqlParameter("@desc", SqlDbType.Text, 100);
idParam.Value = 20;
descParam.Value = "First Region";
command.Parameters.Add(idParam);
command.Parameters.Add(descParam);

// Call Prepare after setting the Commandtext and Parameters.
command.Prepare();
command.ExecuteNonQuery();

SQL injection prevention by replacing ' with ''

1 Answers1