How to prevent SQL Injection in a search form in asp

Question

I am working on a news based site. And the site has a search bar for the Newstitle and I don't want to let SQL injections happen on it.

What I am doing is to get the text from the textbox and then use a query to fetch the matching results. This is what happens when a user clicks the search button:

protected void button_Click(object sender, EventArgs e)
{
        string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);

        try
        {
            SqlCommand comm = new SqlCommand("SELECT * FROM news
                Where newstilte LIKE '%" + searchbox.text + "%'", conn);
            conn.Open();

            SqlDataReader reader = comm.ExecuteReader();

            myRepeater.DataSource = reader;
            myRepeater.DataBind();

            reader.Close();
        }
        catch (Exception exception)
        {
            Response.Write(exception.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

As you can see I then use a repeater to show the results. I am wondering how can I prevent SQL injection in the part where people write in the textbox.

Using [parameterized queries](http://www.codinghorror.com/blog/2005/04/give-me-parameterized-sql-or-give-me-death.html)? — Soner Gönül, Jul 01 '13 at 08:35
If you know about sql injection, have you tried typing that into [google](https://www.google.com/search?q=asp+net+sql+injection)? — Michal Klouda, Jul 01 '13 at 08:37
@SonerGönül +1 for mentioning that excellent article (by no less than the SO founder). — Marcello Romani, Jul 01 '13 at 08:38
How about a 5-sec search on this very site ? http://stackoverflow.com/questions/4018174/preventing-sql-injection-in-asp-net-vb-net — Marcello Romani, Jul 01 '13 at 08:42
I don't see how parameterized queries are going to help.The tilte is a string.I should add a parametr to the query with a nvchar(50) type for example.the hacker can still insert some bad code in it and it is still a nvarchar(50)! — roostaamir, Jul 01 '13 at 08:45
@user2978785: The parameterized query will prevent the interpretation of the parameter text as SQL. It will be interpreted only as string thus preventing the SQL injection. Just try it out and see for yourself - and read that article! — TToni, Jul 01 '13 at 08:51
@user2078785 - It doesn't matter if the parameter contains SQL code as it is never executed. The parameter is treated as data except if you were to `EXEC` it. — Martin Smith, Jul 01 '13 at 08:52

C Sharper · Accepted Answer · 2013-07-01T08:54:36.593

3

USE PARAMETRIZED QUERIES AS BELOW:

protected void button_Click(object sender, EventArgs e)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
        SqlConnection conn = new SqlConnection(connectionString);
        try
        {
            SqlCommand comm = new SqlCommand("SELECT * FROM news
                Where newstilte LIKE '%' + @newstilte + '%'", conn);

            cmm.Parameters.AddWithValue("@search",searchbox.text)  ;

            conn.Open();
            SqlDataAdapter reader = comm.ExecuteReader();
            myRepeater.DataSource = reader;
            myRepeater.DataBind();
            reader.Close();
        }
        catch (Exception exception)
        {
            Response.Write(exception.ToString());
        }
        finally
        {
            conn.Close();
        }
    }

EDIT:

You can also use following if you have datatype kind of restriction for search.

cmm.Parameters.Add(new SqlParameter("@search", SqlDbType.VarChar));
cmm.Parameters["@search"].Value = searchbox.text;

Have a look at THIS doccument.

edited Jul 01 '13 at 08:54

answered Jul 01 '13 at 08:40

C Sharper

8,284
26
88
151

even better if he makes the following a function or a stored procedure in order to validate the input Type (int,float,nvarchar etc) – czioutas Jul 01 '13 at 08:42
4

You mean `LIKE @search` or `LIKE '%' + @search + '%'` – Marc Gravell Jul 01 '13 at 08:43
1

@drakoumelitos - There is no need to validate the input type. It is going to be a string. From `searchbox.text` – Martin Smith Jul 01 '13 at 08:45
I don't see how parameterized queries are going to help.The tilte is a string.I should add a parametr to the query with a nvchar(50) type for example.the hacker can still insert some bad code in it and it is still a nvarchar(50)!How is it going to help? – roostaamir Jul 01 '13 at 08:48
5

@user2078785 because parameters are **not treated as part of the query**; they are *data*. The hacker can **not** inject code here, because the `mwah'-- evil code here` is treated simply as data, not as commands. – Marc Gravell Jul 01 '13 at 08:53
@user2078785 see my edited alternative [ Marc Gravell's comment tells you exact reason, but still edited thing can be more restrictive in your doubt] – C Sharper Jul 01 '13 at 08:54

score 1 · Answer 2 · answered Jul 01 '13 at 08:40

1

Try

 SqlCommand comm = new SqlCommand("SELECT * FROM news
            Where newstilte LIKE '%' + @newstilte + '%'", conn);

 comm.Parameters.AddWithValue("@newstilte",searchbox.text)

answered Jul 01 '13 at 08:40

kostas ch.

1,960
1
17
30

3

You mean `LIKE @newstilte`, or possibly `LIKE '%' + @newstilte + '%'` – Marc Gravell Jul 01 '13 at 08:41

score 1 · Answer 3 · edited Jul 01 '13 at 08:42

1

Use stored procedures with parameters.

.net SQL library properly

SqlCommand comm = new SqlCommand("StoredProcedureName")
comm.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Parameter", Value)

The .net library should handle most injections.

edited Jul 01 '13 at 08:42

marc_s

732,580
175
1,330
1,459

answered Jul 01 '13 at 08:41

czioutas

1,032
2
11
37

2

There is no need to jump to stored procedures; they have virtually no benefit **whatsoever** over correctly parameterized queries, and many advantages – Marc Gravell Jul 01 '13 at 08:42
they include parameter type validation – czioutas Jul 01 '13 at 08:43
not to mention they improve speed of queries because of the logic behind mssql – czioutas Jul 01 '13 at 08:44
most cast operations that would have worked in regular SQL will also apply to parameter types, and frankly - it still isn't a big plus – Marc Gravell Jul 01 '13 at 08:45
no, that is the urban legend that hasn't been true since SQL Server 6.5? SQL Server 7? Stored procedures and parameterized commands use the *exact same* query plan cache. They do **not** give you a performance advantage – Marc Gravell Jul 01 '13 at 08:46
well StoredProcedures give you better security (users don't need direct table access) also they are compiled in the DB before hand and create cashing of query map, i really thing all these are a big plus if you are going for a commercial webapp – czioutas Jul 01 '13 at 08:48
its not that they give you a different query execute plan but the query plan is being cashed thus not having to create it again – czioutas Jul 01 '13 at 08:49
1

if your users even have *sproc* access to the database, you've already lost the battle. The "compiled in the DB before hand" and "the query plan is being cached thus not having to create it again" - to repeat: they use the **exact same mechanism**; there is no difference between them here - it will behave very similarly in both cases. And, hint: I *do* work on commercial webapps ;p There are scenarios where they have utility, but: they are the exception, not the norm – Marc Gravell Jul 01 '13 at 08:50
well at the end of the day i dont see any cons over not using a stored procedure and also no case where a non-stored procedure would be faster than a stored procedure or function, wasnt going personal about the commercial web app just generally speaking. – czioutas Jul 01 '13 at 08:53
the cons are simple: deployment is significantly easier with parameterized queries, especially in a farm environment; if the calling app owns the query, you never need to worry about breaking changes between the query and the caller - conversely, with a sproc you need to plan very carefully for some changes "sproc first / last?" etc. Very tricky in large farms. You also have the ability to perform "smart" queries (appropriate clauses etc) without having to drop to `sp_executesql` (t-sql is not really designed for meta-programming) – Marc Gravell Jul 01 '13 at 08:56

How to prevent SQL Injection in a search form in asp

3 Answers3