locked
Is there something wrong with this SQL statement? RRS feed

  • Question

  • User1000945240 posted

    "SELECT [SubscriptionEnds] FROM [HotzMembers] WHERE [UserEmail] =" + strUserEmail

    In the database UserEmail is stored as a varchar(50)

    I'm getting the error:

    System.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "scancap@googlemail.com" could not be bound. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean

    etc. etc. for a page or so.

    Here is the relevant code

           strSQL = "SELECT [SubscriptionEnds] FROM [HotzMembers] WHERE [UserEmail] =" + strUserEmail
            dsMember.SelectCommand = strSQL
            Dim dv As DataView, iClash As Int16
            'Try
            dv = CType(dsMember.Select(DataSourceSelectArguments.Empty), DataView)

    It's failing on that last line

    Tuesday, November 18, 2014 5:57 PM

Answers

  • User-1199946673 posted

    You need to use the string delimiter (single quotes)

    The resulting string should look like:

    strSQL = "SELECT [SubscriptionEnds] FROM [HotzMembers] WHERE [UserEmail] = 'scancap@googlemail.com'"

    However. your code is vulnarable for SQL injections. You better use parameterized queries. Another advantage is that you don't need to worry about delimiters

    http://www.mikesdotnetting.com/article/113/preventing-sql-injection-in-asp-net

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2014 6:14 PM
  • User-1199946673 posted

    I do use parameters where needed. In this case the email address has been pulled from the database prior to this and had been originally entered via a parameter.

    In the case of an email address, this might work. But what if the value includes a single quote? That will result in a runtime erro as well.

    Start using Parameterized queries ALWAYS, even when you think you don't need to. Sooner or later you'll make the mistake not to use them, thinking you didn't need to, and your database get's attacked because hackers found the hole. Better save than sorry....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2014 6:40 PM

All replies

  • User-1199946673 posted

    You need to use the string delimiter (single quotes)

    The resulting string should look like:

    strSQL = "SELECT [SubscriptionEnds] FROM [HotzMembers] WHERE [UserEmail] = 'scancap@googlemail.com'"

    However. your code is vulnarable for SQL injections. You better use parameterized queries. Another advantage is that you don't need to worry about delimiters

    http://www.mikesdotnetting.com/article/113/preventing-sql-injection-in-asp-net

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2014 6:14 PM
  • User1000945240 posted

    Thank you. That was it.

    I do use parameters where needed. In this case the email address has been pulled from the database prior to this and had been originally entered via a parameter.

    Tuesday, November 18, 2014 6:30 PM
  • User-1199946673 posted

    I do use parameters where needed. In this case the email address has been pulled from the database prior to this and had been originally entered via a parameter.

    In the case of an email address, this might work. But what if the value includes a single quote? That will result in a runtime erro as well.

    Start using Parameterized queries ALWAYS, even when you think you don't need to. Sooner or later you'll make the mistake not to use them, thinking you didn't need to, and your database get's attacked because hackers found the hole. Better save than sorry....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 18, 2014 6:40 PM