SQL Query: ASP Classic 3.0 vs ASP NET 4 (C#)

[cms9651]

Hi there, I need your help.
Here is my problem.

I tried this query example in ASP Classic 3.0 and dbms SQL Server 2008:

strSQL = " SELECT COUNT(*)  FROM dotable "
strSQL = strSQL & " WHERE  1 "
 
If Request.Querystring("MA_Cod") <> "" then
   strSQL = strSQL & " AND MA_Cod ='" & trim(Request.Querystring("MA_Cod")) & "' "
end if

strSQL = strSQL & " GROUP BY "

If Request.Querystring("MA_Cod") <> "" then
   strSQL = strSQL & "   MA_Cod; "
ElseIf Request.Querystring("TR_Cod") <> "" then
   strSQL = strSQL & "   TR_Cod; "
End If

How to reproduce the same query where condition and the same output in code-behind of .net (C#) ?
It's possible?

Can you help me?
Thanks in advance.

[wwb_99]

First, you should not recreate that code at all -- it is a sql injection waiting to happen. Sanitize your inputs. And then use parameters.

As for the code itself, I'm not sure what the point of the WHERE 1 is -- can you explain?

[cms9651]

thank you for reply, you have right but query is example...
I want to know now if you can use the same syntax in code-behind of my net page...

Write "if" condition to internal query ...

[wwb_99]

Yes, C# is a turing complete programming language that allows one to use things like if statements. You could use VB.NET and perhaps copy / paste in the code, even if it is that bad.

The query is presumably being executed on the same data platform so if you generate the same sql it should work.

[cpradio]

As for the code itself, I'm not sure what the point of the WHERE 1 is -- can you explain?

This is a technique most companies use when needing to build dynamic where clauses. In short, if the form being submitted wouldn't generate WHERE conditions, the reamining SQL concatenation will continue to work.

Granted you can check to see if a WHERE condition applies before writing WHERE in your SQL string, but for whatever reason, this is how many companies do it. I don't quite get it myself, I prefer stored procedures, but alas this technique does work.

strSQL = " SELECT COUNT(*)  FROM dotable "
strSQL = strSQL & " WHERE  1 "
 
If Request.Querystring("MA_Cod") <> "" then
   strSQL = strSQL & " AND MA_Cod ='" & trim(Request.Querystring("MA_Cod")) & "' "
end if

strSQL = strSQL & " GROUP BY "

If Request.Querystring("MA_Cod") <> "" then
   strSQL = strSQL & "   MA_Cod; "
ElseIf Request.Querystring("TR_Cod") <> "" then
   strSQL = strSQL & "   TR_Cod; "
End If

What you have is valid VB.NET, if you want to use C#, then the following will do exactly what you have already coded. However, I must agree with wwb_99, that you are open to a SQL Injection and should really look into use the Microsoft AntiXSS library to help protect against this vulnerability or to parameterize your query.

string strSQL = " SELECT COUNT(*)  FROM dotable WHERE  1 ";
 
if (Request.Querystring("MA_Cod") != "")
   strSQL += " AND MA_Cod ='" + trim(Request.Querystring("MA_Cod")) + "' ";

strSQL += " GROUP BY ";

if (Request.Querystring("MA_Cod") != "")
   strSQL += "   MA_Cod; ";
else if (Request.Querystring("TR_Cod") != "")
   strSQL += "   TR_Cod; ";

[cms9651]

thanks a lot for help !