WebGrid with Select * Like *

Question

I use a WebGrid to display the result of:

select * from table where column like '**';

There is no problem displaying the result.

However, if I click the column name of the WebGrid to sort the results, I will get an error or there is nothing displayed on page.

What can I do to make the WebGrid sorting work ??

    @{
        Layout = "~/_SiteLayout.cshtml";
        Page.Title = "Test"; 

        var value = "";
        var sqlquery = "";
        var isvalid = true;
        var searchaskey = "";
        var grid = new WebGrid();

        if(IsPost)
        {
            value = Request.Form["input"];
            if(string.IsNullOrEmpty(value))
            {
                isvalid = false;
            }
            if (isvalid)
            {
                searchaskey = "'%" + value + "%'";
                sqlquery = "select * from joblist where JobCategory like " + searchaskey;
                var db = Database.Open("StarterSite");
                grid = new WebGrid( db.Query(sqlquery));
            }
        }
    }


<form method="post" action="Test.cshtml">
@value
@sqlquery
<div>
<input name="input" type="text" value=@value>
<button type="Submit">Submit</button>
</div>
</form>

    @{
        if(IsPost)
        {
            @grid.GetHtml(
                columns:grid.Columns(
                    grid.Column(columnName : "JobTitle",header:"  Job Title  "),
                    grid.Column(columnName : "JobCategory",header:"  Job Category  "),
                    grid.Column(columnName : "CompanyName",header:"  Company Name  "),
                    grid.Column(columnName : "PostedOn",header:"  Post Date  ")
                )
            )
        }
    }

score 0 · Answer 1 · answered May 10 '12 at 11:41

The way that the webGrid works is when you click on a column to resort, the browser does a GET back to the server to find the new list according to the new sort column. However, your code is testing to see if it's a POST and not returning any results if it's not a post.

To fix the problem, on a POST your code needs to save the search critera so it can use the search critera on subsequent GETs. You'd do this

Session["MySavedSearchAsKey"] = searchaskey;

Then when there's a GET you'd check to see if Session["MySavedSearchAsKey"] had something in it. If it does, go ahead and run your query and generate the webGrid. If it's empty, then don't do anything.

Finally, your line of code

sqlquery = "select * from joblist where JobCategory like " + searchaskey;

Never build a line of SQL like this. It is trivial to attack this type of code with SQL injection attack. Instead,

sqlquery = "select * from joblist where JobCategory like %0";

results = db.Query( sqlquery, searchaskey);

This is called parameterized SQL and is the safe way to talk to the database from code. The %0 gets replaced with the parameter you pass in the Query statement so it does exactly the same thing as your query, but just in a very safe way.

Good luck!

WebGrid with Select * Like *

1 Answers1