SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    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.
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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?

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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 ...
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,649
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wwb_99 View Post
    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.

    Quote Originally Posted by cms9651 View Post
    Code:
    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.

    Code:
    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; ";

  6. #6
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot for help !
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •