Results 1 to 33 of 33

Thread: SQL Building

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    SQL Building

    Should a string in an sql statement be enclosed in single or double quotes ?

    And does that become the only character in the string that needs to be escaped ?

    Thanks.

  2. #2
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: SQL Building

    You should parameterize your SQL command so that you don't have to worry about escaping the literal. To this for example:
    Code:
    Private Sub InsertUser(ByVal user As String)
        Dim connection As SqlConnection = Nothing
    
        Try
            'Open the connection
            connection = New SqlConnection("My Connection String Here")
    
            'Start the command
            Using cmd As SqlCommand = New SqlCommand("INSERT INTO [MyTable] ([UserColumn]) VALUES (@user);", connection)
                'Setup the parameters
                cmd.Parameters.AddWithValue("@user", user)
    
                'Open up the connection
                connection.Open()
    
                'Execute the Command
                cmd.ExecuteNonQuery()
    
                'Close the connection
                connection.Close()
    
            End Using
        Catch ex As Exception
            'Display the error
            MessageBox.Show(ex.Message)
        Finally
            'Close the connection if it was left open
            If connection IsNot Nothing Then
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
    
                connection.Dispose()
            End If
        End Try
    End Sub
    In the code I pass the parameter user in the SQL and then add the value user from the parameters of the method. This is so that the SQL query is all one string and I'm not escaping anything. It can also handle values such as O'Hare or User--Day.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    That's thrown me a bit dday. I'm not familiar with 'cmd.Parameters.AddWithValue' I have a Select statement and the criteria is entered by the user.
    It's the Where clause I want to construct. Any advice there?

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Building

    entered by the user. -- All the more reason for Parameters! Parameters! Parameters!

    Example:
    Code:
            'Start the command
            Using cmd As SqlCommand = New SqlCommand("Select  Field1, Field2, Field3, Field4 From MyTable Where User = @User", connection)
                'Setup the parameters
                cmd.Parameters.AddWithValue("@User", UserName.Text) 'Assumption - UserName is a text box...
    If you need a reason to use parameters - Take a look at this. It's a bit tongue in cheek... but it's still a very real problem (called SQL Injection).


    Just keep in mind, that when you use AddWithValue it uses the type of the VARIABLE or input value as the datatype for the parameter - it does not know or care about what the database says the field really is.

    This can lead to issues when adding dates or numbers where the input was a text box, or other means where the type may not actually match. In those case you can either use the .Parameters.Add and explicitly set the datatype... or you convert the value to a variable of the appropriate type, then use that with AddWithValue.

    Keep in mind that using parameters does NOT result in simple substitution.... so something like this isn't going to work:
    Code:
            'Start the command
            Using cmd As SqlCommand = New SqlCommand("Select  Field1, Field2, Field3, Field4 From @TableName Where ID = @ID", connection)
                Dim ID as integer
                Integer.TryParse(IDInput.Text, ID) ' Here I convert the text box input to a typed integer variable
                'Setup the parameters
                cmd.Parameters.AddWithValue("@TableName", TableList.Text) 'TableList is a dropdown list box...
                cmd.Parameters.AddWithValue("@ID", ID) 'So here, when I add it, it knows that it is a number and not a string
    I know it seems like it should work. It doesn't. Never has. Never will.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    That's thrown me a bit dday. I'm not familiar with 'cmd.Parameters.AddWithValue' I have a Select statement and the criteria is entered by the user.
    It's the Where clause I want to construct. Any advice there?
    There's no issue constructing a SQL query using conditional logic and string concatenation but that is still not justification for not using parameters. To learn about using parameters in general, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET. Here are two examples of how you might construct queries with parameters, one using dynamic SQL and one using static:
    vb.net Code:
    1. Dim command As New SqlCommand("SELECT * FROM MyTable", connection)
    2. Dim criteria As New List(Of String)
    3.  
    4. If givenNameTextBox.TextLength > 0 Then
    5.     criteria.Add("GivenName = @GivenName")
    6.     command.Parameters.Add("@GivenName", givenNameTextBox.Text)
    7. End If
    8.  
    9. If familyNameTextBox.TextLength > 0 Then
    10.     criteria.Add("FamilyName = @FamilyName")
    11.     command.Parameters.Add("@FamilyName", familyNameTextBox.Text)
    12. End If
    13.  
    14. If criteria.Count > 0 Then
    15.     command.CommandText &= " WHERE " & String.Join(" AND ", criteria)
    16. End If
    vb.net Code:
    1. Dim command As New SqlCommand("SELECT * FROM MyTable WHERE (@GivenName IS NULL OR GivenName = @GivenName) AND (@FamilyName IS NULL OR FamilyName = @FamilyName)", connection)
    2.  
    3. command.Parameters.Add("@GivenName", SqlDbType.VarChar, 50).Value = If(givenNameTextBox.TextLength > 0, CObj(givenNameTextBox.Text), DBNull.Value)
    4. command.Parameters.Add("@FamilyName", SqlDbType.VarChar, 50).Value = If(familyNameTextBox.TextLength > 0, CObj(familyNameTextBox.Text), DBNull.Value)

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Hi Techgnome

    I massively don't get this ! Sorry, not even your link (Some kind of comic ?)

    I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.

    But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?

    I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.

    You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
    My main issue is handling the wrong number of single/double quotes correctly.

    Please expand a bit ?

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Hi jmcilhinney

    Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1

    The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.

    But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
    Yes it's a comic... it shows what can happen if you take user input and just use concatenation to stuff it in there... it can lead to someone wiping out your table or worse, your database. For most of us, it's probably not really that big deal... for some of us it's a HUGE deal... imagine wiping out a company's entire accounting data. Scary stuff. So it's generally a good idea to practice the good habits, no matter how trivial. someone once put it "program as if your life depended on it" ... meaning don't take shortcuts that "just work" ... there are some posts on here (fear not, this isn't one of them) where I think that I need to start taking better care of myself, as I don't want some of these posters here programming my pacemaker with code (or cod) they copied from the forums and just paste into the process...

    Quote Originally Posted by AlexanderBB View Post
    I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.

    But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?
    There's two ways of dealing with user input... 1) is to sanitize it... doubling up quotes... checking for entered escped characters that could lead to broken SQL ... it's tedious and in the end you'll innevitably miss something. Not to mention that when things do go wrong, it's actually more difficult to debug. Or 2) use parameters... then you don't need to do anything to the data, (personal opinion, the less I have to touch the user's data and manipulate it, the better), you simply pass it through and the database engine will take care of it. You don't change it to something that works... that's part of the point.

    Quote Originally Posted by AlexanderBB View Post
    I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.
    Why does the type matter? Because "1" (the string) isn't really the same as 1 (the number) ... Better example - dates.... 1/1/20015 ... if you pass it as the wrong type, you'll either get a really small number (1 divided by 1, dividend by 2015) or you'll end up with a syntax or an invalid value error in your SQL. Again, back to numbers, what if the user enters "007" ... but the field is numbers (so all it has is 7) ... since "007" isn't the same as 7... you won't get back your data.

    Quote Originally Posted by AlexanderBB View Post
    You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
    My main issue is handling the wrong number of single/double quotes correctly.

    Please expand a bit ?
    Well, we've all been there at some point... takes time and practice.
    As for the wrong number of quotes.... Ahhh see young grasshopper, buy using parameters, one does NOT need to care about quotes...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    Hi jmcilhinney

    Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1

    The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please
    This is basic ADO.NET. You should know by this stage that if you are creating a command object then you pass a SQL statement and a connection object to the constructor. That's not even a relevant part of the example; it's just there for completeness. If you don't know how to create an ADO.NET command then how can you possibly hope to execute one with SQL code constructed dynamically?

    If you don't know the basics of ADO.NET then you should learn that first. You might follow the Database FAQ link in my signature below and check out the .NET links there, which includes my own code examples for the most common ADO.NET scenarios.

  10. #10

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    All read and digested tg. Thank you. But datatypes.. I'm validating the user input data so a number is a number, a date a data and everything else is a string.
    I may have up to 20 "Where" clauses to sort out so was pleased to read from jmcilhinney there's "no issue constructing a SQL query using conditional logic and string concatenation"
    My data is not "injection critical' but I'm all for "not caring about quotes" so will plug on figuring out what this magic parameter stuff is. It's a mystery what the "@" is
    e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....

  11. #11
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    Hi jmcilhinney

    Tahnks.. I'm trying to step through your example but have struck a snag with "Connection" on line 1

    The Sql connection string here (date Source?) is too long. Could you give me a new line 1 using ole command please
    Or are you referring to the second example and saying that the SQL statement that you are using in your own code is too long? A SQL statement is not a connection string and if it's too long then it doesn't matter what provider it's for. In that case the obvious option is to use the first example, which results in shorter SQL.

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    I may have up to 20 "Where" clauses to sort out so was pleased to read from jmcilhinney there's "no issue constructing a SQL query using conditional logic and string concatenation"
    No, you can never have more than one WHERE clause. Perhaps you mean that you may have that many criteria in the WHERE clause.
    Quote Originally Posted by AlexanderBB View Post
    It's a mystery what the "@" is
    e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....
    If that's the case then you have chosen to ignore the advice I provided:
    To learn about using parameters in general, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.
    If you're not going to bother to read information when you're directed to it then I don't see why I should continue to bother volunteering my time to help.

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    > That's not even a relevant part of the example; it's just there for completeness.

    Yes I know, but I haven't got an example to hand and wanted to step through the rest of and learn something. I'll get one, just mot quickly.

    Have got thousands of ADO connection strings but used in Access and almost everything is different in vbNet.

  14. #14

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    jmcilhinney it was your blog I referred to in msg #6. I didn't make a lot of sense to me, but I know that's me - not your blog!

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    jmcilhinney it was your blog I referred to in msg #6. I didn't make a lot of sense to me, but I know that's me - not your blog!
    Post #6 was made 9 minutes after my post suggesting that you read my blog post. I would expect it to take at least 9 minutes to read that blog post, never mind understand it. You're basically saying to us that we should spend more of our time to explain something to you that we've already taken the time to explain elsewhere because you don't want to spend your time reading that original explanation. You'll excuse me if I consider that an unreasonable request on your part. I take the time to write blog posts like that specifically so that I don't have to keep addressing the same questions over and over. Make use of the information available or do without because I won't be contributing further.

  16. #16

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    jmcilhinney, I read your blog about a month ago, 2 or 3 times in a row a I remember thinking ' I wish I knew what this guy knows" but really it make little sense to me and there was nothing I could get from it. That may happen further down the track. I defer to your superior knowledge and certainly appreciate everyone's help on the forum (and there's been a lot of it and it's great).

    I'll get there but I have to interpret and understand things my way, not your way. Examples that you can step through are my favourite.
    Once the connection string sorted I'll see just what your msg 5 is doing. You could have explained that ... I was trying avoid sidetracking to that while keen to see what this "@" is... . I had an sql Connction string a few days ago, but the path was too long, so opted to keep ole connection details instead.

    Anyway, cherioo and thanks for your help in the past. No more darkening doorstep etc !

  17. #17
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    My data is not "injection critical' but I'm all for "not caring about quotes" so will plug on figuring out what this magic parameter stuff is. It's a mystery what the "@" is
    e.g. "GivenName = @GivenName" deosn't mean a thing. I'm keen to see... from the example in msg 5 it must be a critical but how @GivenName can become injection safe, and avoid any quotes issue....
    '@GivenName' is simply a placeholder, otherwise known as a parameter. This allows you to provide a value to it for use at execution.

    'GivenName' without the @ sign simply refers to a column within the table upon which the query is being executed.

    Parameters can be used as a way to pass values safely into your SQL command String.

    the Comand.Parameters.AddWithValue method is structured as follows:

    Comand.Parameters.AddWithValue(<Parameter-Name>,<Value>)

    <Parameter-Name> is the name which you have given your parameter within the SQL string

    <Value> is the value you would like that parameter to be replaced with when the query is executed.




    For example

    "SELECT * FROM PEOPLE WHERE NAME= 'Dave';"

    Is the same as

    "SELECT * FROM PEOPLE WHERE NAME= @Name;"
    Comand.Parameters.AddWithValue("@Name","Dave")


    Alternatively, using a textbox

    DONT DO THIS
    "SELECT * FROM PEOPLE WHERE NAME= '" & TextBox1.Value "';"

    Instead Do This
    "SELECT * FROM PEOPLE WHERE NAME= @Name;"
    Comand.Parameters.AddWithValue("@Name",Textbox1.Value)

    I Hope this clears up any confusion
    Last edited by Leary222; May 28th, 2015 at 04:20 PM. Reason: Clairty

  18. #18
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: SQL Building

    Hi Techgnome

    I massively don't get this ! Sorry, not even your link (Some kind of comic ?)

    I did read a bit about SQL injection and parameters. It left me really msytified. As I understand it, you replace what the user enters with something else.

    But if you need what they enter, and have no way of knowing what it is, how can you change it to something else that works?

    I've never used cmd.Parameters.AddWithValue so will have to figure what that does. But why care about datatypes? If the wrong thing is entered, it probably won't match and they'll get no result.

    You guys know so much more than me, that my assumptions must be wrong or flawed, but I don't see where yet !
    My main issue is handling the wrong number of single/double quotes correctly.

    Please expand a bit ?
    It sounds like you are suffering form Information overload.

    Read Leary222 post just above this one for a nice easy clear explanation on Parameters

    Try and get that bit down first forget the rest, just read Leary222's post a few times and try it out!!!

    It's a mystery what the "@" is
    e.g. "GivenName = @GivenName" deosn't mean a thing.
    Don't over think this, @GivenName is just a parameters placeholder for your data, nothing more!!!

    When you do this -
    Code:
    Parameters.AddWithValue("@Name","Dave")
    You are saying @Name equals 'Dave', you can then use @Name in your SQL statement instead of the actual string 'Dave'.
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  19. #19

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Thanks Leary, that's a nice rundown . So whatever is entered by the user is enclosed in quotes and thus cannot be interpreted
    as an instruction that might be malicious? Is that right ?

    The way I'm building up my sql statement is

    Code:
     If Not txtArtist.Text = String.Empty Then
                AddBit = "(Artist ? '~" & txtArtist.Text & "~') And "
                    If cboArtist.Text = "is" Then
                        AddBit = Replace(AddBit, "?", "=")
                        AddBit = Replace(AddBit, "~", "")
                    ElseIf cboArtist.Text = "contains" Then
                        AddBit = Replace(AddBit, "?", "Like")
                        AddBit = Replace(AddBit, "~", "%")
                End If
                    buildstring = buildstring & AddBit
            End If
    A combo box offers "is" or "contains" which has to be selected before the text box txtArtist is enabled.
    That structure is repeated for whatever criteria is input by the user (altered for dates or numbers).

    I had a go at changing line 2 to (trial and error at this stage!)
    AddBit = "(Artist ? '~@name~') And "

    But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'


    Also if the user enters don"t wouldn't that become "don"t" and (unless I'm wrong) cause an error ?
    I've been using single quotes (easier to type) and wondering about replacing them with Chr(34) when the statement is complete.

    Either way, does it not mean escaping with double characters is required ?

  20. #20

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Hi NeedSomeAnswers, thanks for input... I'm getting some myself now! Not out of the woods but can see light

    Cheers, ABB

  21. #21
    PowerPoster Evil_Giraffe's Avatar
    Join Date
    Aug 2002
    Location
    Suffolk, UK
    Posts
    2,555

    Re: SQL Building

    There's still a bit of confusion here. First up, you should probably understand that when you are using a parameterised query, that is not working out how to do the escaping of the string for you or doing any sort of replacement of the SQL in your code. The query is passed as-is to the SQL engine which parses the query, and sees that there is a parameter in it. That is how it can contribute to a defence against SQLi. SQLi is all about escaping from the data context into the query context: there is some text that you intend to only be data, but if it is crafted in a certain way it can trick the SQL parser into thinking it is part of the query and thus alter it. With parameters the user-entered data is never considered by the parser and therefore it can't escape the data context.

    So, now we are looking at your specific criteria you want to build up dynamically. for the outer-most part of this, jmc has already shown you one approach that works for conditionally including a criteria in post #5. So you'll want something that looks like:
    Code:
    Dim criteria As New List(Of String)
    If Not txtArtist.Text = String.Empty Then
        criteria.Add(*** something ***)
        command.Parameters.Add("@ArtistName", txtArtist.Text)
    End If
    The next issue is that you want two different criteria depending on what the combo box is. Not a problem though, you should be able to apply the inner bit in two different ways:
    Code:
    Dim criteria As New List(Of String)
    If Not txtArtist.Text = String.Empty Then
        If cboArtist.Text = "is" Then
            criteria.Add(*** something ***)
            command.Parameters.Add("@ArtistName", txtArtist.Text)
        ElseIf cboArtist.Text = "contains" Then
            criteria.Add(*** something ***)
            command.Parameters.Add("@ArtistName", txtArtist.Text)
        End If
    End If
    Okay,but what are we doing in each case? The first is easy, it's a normal straight comparison:
    Code:
    Dim criteria As New List(Of String)
    If Not txtArtist.Text = String.Empty Then
        If cboArtist.Text = "is" Then
            criteria.Add("Artist = @ArtistName")
            command.Parameters.Add("@ArtistName", txtArtist.Text)
        ElseIf cboArtist.Text = "contains" Then
            criteria.Add(*** something ***)
            command.Parameters.Add("@ArtistName", txtArtist.Text)
        End If
    End If
    The next needs slightly careful thinking about. Remember that the parameter contains the entire value of data. The wildcards in a LIKE are definitely part of the data, so we can't specify them in the query string, they must come from the parameter, but equally we don't want the user to be typing them. The answer, of course is that the parameter value doesn't need to be exactly what the user entered - we can add the % characters before populating the value:
    Code:
    Dim criteria As New List(Of String)
    If Not txtArtist.Text = String.Empty Then
        If cboArtist.Text = "is" Then
            criteria.Add("Artist = @ArtistName")
            command.Parameters.Add("@ArtistName", txtArtist.Text)
        ElseIf cboArtist.Text = "contains" Then
            criteria.Add("Artist LIKE @ArtistName")
            command.Parameters.Add("@ArtistName", "%" + txtArtist.Text + "%")
        End If
    End If
    Okay, fine? Well, not quite. We've avoided the ability for user input to escape into the query context, but they can still escape into the LIKE context. For this you'd need to escape the input string, but as it's a much more constrained language it's somewhat easier and therefore safer, and if you get it wrong you won't endanger your database in any way, just get some odd results from your query. A quick search yields this page which may help, depending on what your database system is: http://stackoverflow.com/questions/2...ike-expression

  22. #22
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: SQL Building

    But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'
    Thats Becasue Parameters are part of your Command Object so you should be adding them like this -

    Code:
    Command.Parameters.AddWithValue("@Name", txtArtist.Text)

    Lets go back to ddays example right back in post 2 (as imho its a nice simple example) - and i will try and annotate it a bit more

    Code:
    Private Sub InsertUser(ByVal user As String)
        Dim connection As SqlConnection = Nothing                 '******** The Connection Object ***********
    
        Try
            'Open the connection
            connection = New SqlConnection("My Connection String Here")             '******** Set a New Instance of the Connection ***********
    
           '******** Create a Command Object and pass it an SQL statement (substituting your User value for a parameter @user ) ********
    
            Using cmd As SqlCommand = New SqlCommand("INSERT INTO [MyTable] ([UserColumn]) VALUES (@user);", connection)
                
                ''******** Add the Value to the Parameter,  note we are adding to the Parameters collection of cmd - which is the command) ********
                cmd.Parameters.AddWithValue("@user", user)
    
                'Open up the connection
                connection.Open()
    
                'Execute the Command
                cmd.ExecuteNonQuery()
    
                'Close the connection
                connection.Close()
    
            End Using
        Catch ex As Exception
            'Display the error
            MessageBox.Show(ex.Message)
        Finally
            'Close the connection if it was left open
            If connection IsNot Nothing Then
                If connection.State = ConnectionState.Open Then
                    connection.Close()
                End If
    
                connection.Dispose()
            End If
        End Try
    End Sub
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  23. #23
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: SQL Building

    Quote Originally Posted by AlexanderBB View Post
    Thanks Leary, that's a nice rundown . So whatever is entered by the user is enclosed in quotes and thus cannot be interpreted
    as an instruction that might be malicious? Is that right ?
    No, the value passed isn't encapsulated in quotes, as Evil Giraffe said its simply taken out of context; apologies if i was unclear on this.

    The issue with SQL injection is that the command which is sent to the database engine could potentially be modified in order to produce undesired results, nothing to do with quotes being wrapped around the data. By using parameters you are separating the values your user puts in from the command string its self and thus not allowing the quotes to affect the command issued to the database.

    By using parameters the value which you pass is taken literally as just a value and not part of the command string. Therefore should your user put in a value which contains quotes or any other special characters it would not affect the query.

    Quote Originally Posted by AlexanderBB View Post
    But attempts to apply Parameters.AddWithValue("@Name", txtArtist.Text) gave error 'Parameters is not declared...'
    Apologies this may be my fault for missing the Class name from my example. But as identified by needsomeanswers it should be Command.Parameters.AddWithValue("@Name", txtArtist.Text)


    Quote Originally Posted by AlexanderBB View Post
    Also if the user enters don"t wouldn't that become "don"t" and (unless I'm wrong) cause an error ?
    I've been using single quotes (easier to type) and wondering about replacing them with Chr(34) when the statement is complete.

    Either way, does it not mean escaping with double characters is required ?
    No when a user enters any special characters and parameters have been used to pass the values to the database engine they are included as part of the data and do not require any single or double quotes.

    For Example when using parameters

    if txt.Artist.Value = "Don't" then the Value which would be given to the query would be "Don't"

    if you didn't use parameters in this situation you would get an SQL syntax error and the associated exception would be raised to your application.

    in conclusion, if you use parameters quotes wont be required in your SQL command and if the user enters them they will be passed to your database without any issues and a reduced chance of SQL injection.

  24. #24

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Firstly, thanks very much everyone for going to so much trouble explaining this me. I can see I had lots of misconceptions. Not surprising as I'm self taught (or is it self- not taught?).

    I can't explain how I missed the "command" bit as it's blindingly obvious now. Then once it finally sunk in this eliminates any issues with quotes that clinched it as my data is full of them. Evil G said "can escape into the query context, but they can still escape into the LIKE context" so I'll try those two separately. "Like" looks the more complex, especially reading that Stack Overflow link !

    I've also got hard copies of everything to more easily refer back. So here we go!

  25. #25

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Much hair loss later... OK. My design was going to be, build the query in one routine and send it to another which opens the connection
    and send the result to a dgv control.

    But this isn't going to work because the command.parameter thing requires the connection to be already established.

    So the connection is established then the string built up? Yes ?

    Which must all be in the same subroutine or else the textbox values would be out of scope. I had already set up a connection/dgv thing on it's own so my mindset was locked on doing it how I first envisaged. The code for this is (error handling to come later)

    Code:
     Public Sub SQLToGrid(ByVal someSQLQuery As String)
            Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Backend & ";Persist Security Info=False;")
            Dim UserQuery As New OleDbDataAdapter(someSQLQuery, conn)
            Dim UserRet As New DataTable
            UserQuery.Fill(UserRet)
    
            With frmDGV.DataGridView2
                .DataSource = UserRet
            End With
    
            frmDGV.DataGridView2.Visible = True
            frmDGV.Show()
        End Sub
    I can move all this into the same place as everything else.

    I will need an equivalent of 'Dim command As New SqlCommand("SELECT * FROM MyTable", connection)'
    then add command. parameter.... as below .

    Although I'm sure the same line needn't be repeated 3 times, one would do (in this example)
    Code:
            'PEAK
            If Not txtPeaked.Text = String.Empty Then
                If cboPeaked.Text = "at" Then
                    criteria.Add("Val(high) = @High")
                   command.Parameters.AddWithValue("@High",txtPeaked.Text )
                ElseIf cboPeaked.Text = "lower than" Then
                    criteria.Add("Val(high) < @High")
                command.Parameters.AddWithValue("@High",txtPeaked.Text )
                ElseIf cboPeaked.Text = "higher than" Then
                    criteria.Add("Val(high) > @High")
                command.Parameters.AddWithValue("@High",txtPeaked.Text )
                End If
            End If
    Once I can see the full string after all the processing, these individual bits will be much easier to understand.

  26. #26

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    > will need an equivalent of 'Dim command As New SqlCommand("SELECT * FROM MyTable", connection)'

    I think it's Dim command As New OleDbCommand(Selectstring, conn) . select string is my half built sql string and i'm getting Parameters from iltellisense

  27. #27

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Well this was unexpected! No exceptions but the resultant string has not got the values, but the "@" text e.g.

    WHERE Year = @Year AND Val(high) = @High AND Val(CH) = @CH AND Title = @Title AND Artist = @Artist

    That ain't right, is it ?

  28. #28
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Building

    That is right. The sql string you pass to SqlServer will have the place holders (e.g. @Year) in it. You will also be passing some parameters which specify the values for those place holders.

    SqlServer itself will take those parameters and use those values when it executes the sql string. This happens in the database, not in the client program.

    So for the example above you should be passing 5 parameters. One each for @Year, @High, @CH, @Title and @Artist.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  29. #29

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Hi FD, thanks for that and I never expected that answer! It kind of helps some of the previous explanations
    except that after 2 days I still can't get it to work. And I'm conscious of everyone getting sick and tired
    of me and these crazy questions to them. Otherwise I give up and revert to what I know works, but
    maybe one last try ?

    Essentially are these 2 the same thing ?

    "Select Year from tblMyTest WHERE Year = '1961'"

    "Select Year from tblMyTest WHERE Year = @Year"

    And Year is defined in the code below (with contents of cboYear confirmed as '1961')

    If that's all correct then something must be wrong below as the parameter ones results in 'No value given for one or more required parameters.'
    and the other is OK.

    Code:
    Private Sub Test999()
    
            Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Backend & ";Persist Security Info=False;")
            Dim Selectstring = "Select Year from tblMyTest "
            Dim command As New OleDbCommand(Selectstring, conn)
            Dim criteria As New List(Of String)
    
            If Not cboYear.Text = String.Empty Then
                criteria.Add("Year = @Year")
                command.Parameters.AddWithValue("@High", cboYear.Text)
            End If
    
            If criteria.Count > 0 Then
                command.CommandText &= " WHERE " & String.Join(" AND ", criteria)
            End If
    
            Dim t As String = Replace(command.CommandText, "@Year", "'1961'")
    
            ' Dim UserQuery As New OleDbDataAdapter(command.CommandText, conn)  '<<<  GOES TO ERROR
            Dim UserQuery As New OleDbDataAdapter(t, conn)  '<<<  IS OK
    
            Dim UserRet As New DataTable
            UserQuery.Fill(UserRet)
    
            With frmDGV.DataGridView2
                .DataSource = UserRet
            End With
    
            frmDGV.DataGridView2.Visible = True
            frmDGV.Show()
        End Sub

  30. #30
    PowerPoster Evil_Giraffe's Avatar
    Join Date
    Aug 2002
    Location
    Suffolk, UK
    Posts
    2,555

    Re: SQL Building

    I would look very carefully at this block if I were you:

    Code:
            If Not cboYear.Text = String.Empty Then
                criteria.Add("Year = @Year")
                command.Parameters.AddWithValue("@High", cboYear.Text)
            End If

  31. #31

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    I have '@High' instead of '@Year' ?
    That was a silly mistake I made but even with that corrected I'm still getting "No Value Given...'
    Is there anything else wrong there ?

    Can I ask you about this line in your msg 21 ?

    command.Parameters.Add("@ArtistName", "%" + txtArtist.Text + "%")

    When i enter that I get a message about the command being obsolete ?
    Also, is '+" interchangable with '&' or different altogether?

  32. #32
    PowerPoster Evil_Giraffe's Avatar
    Join Date
    Aug 2002
    Location
    Suffolk, UK
    Posts
    2,555

    Re: SQL Building

    '+' was free-coded in the reply box. Whatever VB's string concatenation operator is. (I'm a C#er by default, sorry!)

  33. #33

    Thread Starter
    Frenzied Member
    Join Date
    Jun 2010
    Posts
    1,462

    Re: SQL Building

    Both + and & brought up the same message, which I'm pretty sure was telling me to use "AddWithValue" syntax but I can't quite
    test it yet ! (But this darn thing has to work in the end!)

    Those examples in your msg 21 esp. about like were great, thanks

    I'm just wondering if the problem is oledb connection as almost all of the examples here use sql.. to use sql I need to move VB into
    a shorter path as I get an error about data source > 127.

Posting Permissions

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



Click Here to Expand Forum to Full Width