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?

Answer 1

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();