5 Replies - 11504 Views - Last Post: 01 April 2010 - 03:12 AM Rate Topic: -----

#1 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

SQL parameters from VB.NET?

Posted 30 March 2010 - 08:32 AM

Hello!

I have this function to retreive stuff from a database. I don't know how to send SQL parameters to the database from VB.NET in combination with using SqlDataAdapter instead of DataReader. Help?

    Public Function GetCountriesTable(ByVal continent As String) As DataTable
        ''----------------------------------------------------------------------
        '' TODO: Risking SQL injections here?
        ''----------------------------------------------------------------------
        Dim connStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("InternalsConnectionString").ToString
        Dim cno As New SqlConnection(connStr)
        Dim query As String
        Dim ds As New DataSet()
        Dim tb As DataTable
        Dim da As SqlDataAdapter

        query = "SELECT Country FROM Countries WHERE continent LIKE '" & continent & "%' ORDER BY Country"

        da = New SqlDataAdapter(query, cno)
        da.Fill(ds)
        tb = ds.Tables(0)

        Return tb 'Returning a DataSet works fine too. Just wanted to try, maybe a little less overhead too.
    End Function



The line "query" doesn't look good to me. Is this where I'm opening up for SQL injection attacks?

Thanks
Jens

Is This A Good Question/Topic? 0
  • +

Replies To: SQL parameters from VB.NET?

#2 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: SQL parameters from VB.NET?

Posted 30 March 2010 - 08:40 AM

Try:

query = "SELECT Country FROM Countries WHERE continent LIKE '" & continent & " % ORDER BY Country" 



That "should" pass as "query = "SELECT Country FROM Countries WHERE continent LIKE Asia% ORDER BY Country"

Put a break point at the next line so the program pauses, then check to see what the query looks like to tell for sure.
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL parameters from VB.NET?

Posted 30 March 2010 - 08:44 AM

Use a SqlCommand Object and use the SqlParameters from it, like this

ublic Function GetCountriesTable(ByVal continent As String) As DataTable
    ''----------------------------------------------------------------------
    '' TODO: Risking SQL injections here?
    ''----------------------------------------------------------------------
    Dim connStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("InternalsConnectionString").ToString
    Dim cno As New SqlConnection(connStr)
    Dim cmd as New SqlCommand()
    Dim query As String
    Dim ds As New DataSet()
    Dim tb As DataTable
    Dim da As SqlDataAdapter

    query = "SELECT Country FROM Countries WHERE continent LIKE @continent ORDER BY Country"
    cmd.CommandType = CommandType.Text
    cmd.CommandText = query
    cmd.Connection = cno
    cmd.Parameters.AddWithValue("@continent, continent)"
    cmd.Connection.Open()		
    da = New SqlDataAdapter(query, cno)
    da.Fill(ds)
    tb = ds.Tables(0)

    Return tb 'Returning a DataSet works fine too. Just wanted to try, maybe a little less overhead too.
End Function


Was This Post Helpful? 1
  • +
  • -

#4 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1606
  • View blog
  • Posts: 5,163
  • Joined: 25-September 09

Re: SQL parameters from VB.NET?

Posted 30 March 2010 - 09:04 AM

PsychoCoder
cmd.Parameters.AddWithValue("@continent, continent)"
I have a question on using the LIKE operator with the parameter.

Of course, I fixed a typo with the quote but does the parameter assume the % based on the LIKE in the query or does it have to be appended to the variable as such?
cmd.Parameters.AddWithValue("@continent", continent & "%")



Also, do you know what the difference is for Parameters.Add and Parameters.AddWithValue? As both seem to allow you to put the value of the parameter in. The help doesn't seem to clarify when to use what and why?

This post has been edited by CharlieMay: 30 March 2010 - 09:05 AM

Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1642
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: SQL parameters from VB.NET?

Posted 30 March 2010 - 09:12 AM

For starters Add is Deprecated

The difference is in performance and the ability for SQL to actually cache ado sql commands.../ queries. When you use Parameters.Add SQL has to box and unbox to find out what sqldatatype the parameter is, when you use Parameters.AddWithValue - you explicitly set the sqldb.type and SQL knows exactly the dbtype when passed.

As for the other question, no SQL does not assume the %, that's my bad for forgetting it. Use this

query = "SELECT Country FROM Countries WHERE continent LIKE @continent " & '% ORDER BY Country"


Was This Post Helpful? 1
  • +
  • -

#6 jens  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 67
  • View blog
  • Posts: 430
  • Joined: 09-May 08

Re: SQL parameters from VB.NET?

Posted 01 April 2010 - 03:12 AM

Hi!

I'm having a bad time with this one...

I didn't get the suggested code to work since there seems to be some confusion with the ":s and ':s but that's not the problem.

The problem is that there's somthing weird going on with the @continent thingy.

Here is my code
    Public Function GetCountriesTable(ByVal continent As String) As DataTable
        ''----------------------------------------------------------------------
        '' TODO: Risking SQL injections here?
        ''----------------------------------------------------------------------
        Dim connStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("InternalsConnectionString").ToString
        Dim cno As New SqlConnection(connStr)
        Dim cmd As New SqlCommand
        Dim query As String
        Dim ds As New DataSet()
        Dim tb As DataTable
        Dim da As SqlDataAdapter

        'query = "SELECT Country FROM Countries WHERE continent LIKE '" & continent & "%' ORDER BY Country"

        cmd.Parameters.AddWithValue("@continent", continent)
        query = "SELECT Country FROM Countries WHERE (continent = @continent) ORDER BY Country"
        cmd.CommandType = CommandType.Text
        cmd.CommandText = query
        cmd.Connection = cno

        da = New SqlDataAdapter(query, cno)
        da.Fill(ds)
        tb = ds.Tables(0)

        Return tb 'Returning a DataSet works fine too. Just wanted to try, maybe a little less overhead too.
    End Function



And here is the error (Must declare the scalar variable "@continent".)Attached Image

No problem if I use a DataReader but the combination of DataAdapter and SqlCommand.CommandText seems to be kind of whacky.

How do I get "@somthing"-parameters into the query string?

Regards
Jens
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1