ASP.Net Webpages/SQL Query question

daver · 4 Jul 2014 at 22:48

Been looking at writing a few simple webpages to display KPI information from an SQL server database and come across an issue when trying to set up an orderbook search page.

I basically have a text box on a page which I want to query the database to return job numbers which match the entry. At the moment to make things simple I just want to search on JobNumber and return that and Outstanding Qty.

I keep getting an error 'Conversion failed when converting the nvarchar value '7036/10' to data type int'. I know this is because the job number field has none integers, if I switch the parameter to oustanding_qty I can search on that fine.

It's definitely something massively simple, but being a noob i just can't see what it is?

My current code is in the spoiler tags. It is something to do with the way i am either defining the JobNumber input or referencing it in the query.

@{Layout="Layout.cshtml";
var db = Database.Open("*****");
string JobNumber = Request.Form["JobNumber"];
var OrderbookQuery = @"
SELECT Orde_Goods.Alternate_Job_Number, ORDE_Goods.Outstanding_Qty FROM ORDE_Goods WHERE ((ORDE_Goods.Outstanding_Qty>0)) AND (Orde_Goods.Alternate_Job_Number="+JobNumber+")";
}

<!doctype=html>
<html><head>
<link rel="stylesheet" type="text/css" href="Site.css">
</head>
<body>
<form method="post" action="">
<fieldset>
<legend>Select Job No.</legend>
<div>
<label>Job Number</label>
<input name="JobNumber" type="text" size="5" value="@JobNumber"/>
</div>
<div>
<label> </label>
<input type="submit" value="Submit" class="submit" />
</div>
</fieldset>
</form>

@{if(IsPost)
{
<table border="1">
<tr>
<th>Job Number</th>
<th>Outstanding Qty</th>
</tr>
@foreach(var row in db.Query(OrderbookQuery))
{
<tr>
<td>@row.Alternate_Job_Number</td>
<td>@row.Oustanding_Qty</td>
</tr>
}
</table>
}
}
</body>
</html>

touch · 5 Jul 2014 at 00:36

Maybe need to include quotes around your search in the query?
If you type in the search 7036/10 this line:

(Orde_Goods.Alternate_Job_Number="+JobNumber+")";

Will create a query like this:

(Orde_Goods.Alternate_Job_Number=7036/10)

But you need it with quotes around the string:

(Orde_Goods.Alternate_Job_Number="7036/10")

daver · 5 Jul 2014 at 12:05

Thank you. Changed the line to (Orde_Goods.Alternate_Job_Number = '"+JobNumber+"') and it works. knew it would something simple

Haircut · 5 Jul 2014 at 13:02

Could you use a parameter for the query instead?
That version looks horribly open to SQL injection attack the way you have written it.

(I'm assuming that the JobNumber variable is taken straight from an input form?)

daver · 5 Jul 2014 at 16:22

Yes that was my next step - I have replaced the two lines with

Orde_Goods.Alternate_Job_Number =@0
and
@foreach(var row in db.Query(OrderbookQuery, JobNumber))

Which seems to work fine - this would avoid the problem of injection right?

Fallen god · 5 Jul 2014 at 17:40

daver said:
Yes that was my next step - I have replaced the two lines with

Orde_Goods.Alternate_Job_Number =@0
and
@foreach(var row in db.Query(OrderbookQuery, JobNumber))

Which seems to work fine - this would avoid the problem of injection right?

I don't know how to program in ASP but the injection can happen due to you taken direct input, so if as a user I put in some really bad sql into the box e.g. ; drop database.

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

This article covers what you need to do, if it was PHP I could offer more guidance.

touch · 5 Jul 2014 at 18:55

You should make the query a stored procedure on the server and pass the paramaters to it.

ilcp · 7 Jul 2014 at 05:58

i thought only PHP programmers wrote spaghetti code..

Mr^B · 7 Jul 2014 at 19:31

Just because...

davetherave2 · 7 Jul 2014 at 20:09

A brilliant website to go and look at is www.troyhunt.com he goes over these sort of SQL injection attacks all the time and explains it really nicely to the non-security conscious developer.

As others have said I would suggest either changing the sql statement to a stored procedure or even better use an ORM like Entity Framework etc.

I am guessing with your page set up as it is you are using webmatrix (I only ever seem to see this sort of thing in webmatrix demos)? if not then I would suggest doing all the database stuff either in the code behind (webforms) or in the controller (MVC). This way you could at least perform some validation on the user entered data being inputted and clean it before it is executed.

Glad you managed to sort your problem out anyway.