AddWithValue to SQL Statements? VB.Net

Question

I have a bunch of AddWithValue statements. I have been racking my brain to figure out a way to change them into SQL Statements.

This is what it currently looks like;

Dim updateStatement As String =
        "UPDATE Customers SET " &
        "Name = @NewName, "
        "WHERE Name = @OldName "

    Dim updateCommand As New OleDbCommand(updateStatement, connection)
    updateCommand.Parameters.AddWithValue("@NewName", newCustomer.Name)
    updateCommand.Parameters.AddWithValue("@OldName", oldCustomer.Name)

'Connection Open blah blah blah Connection Closed.

I was told a different way of doing it was;

Dim SQL as String = "UPDATE Customers SET" &
"Name = '" Customer.Name "', "

But it refuses to work this way saying that just the two first double quotations is it.

"Name = '" Customer.Name "'," In italics the code is black and not red which refuses to work.

I am not using a database, I am simply pulling it from the file itself (As if it were in Access)

Help me with whatever you can! I wish I could just leave it because if it's not broken don't fix it. But my teacher has a funny way of messing with people.

Your original way is correct. Don't try to concatenate queries though. It makes the code vulnerable to SQL injection. Who told you it was better? — Szymon, Apr 01 '14 at 09:45
Imagine, `Customer.Name = "ABC'; DELETE FROM Customers --";` — Hamlet Hakobyan, Apr 01 '14 at 09:49
I know the original way is correct. I'm supposed to learn different ways. Believe me. I'd much rather keep it the way it is. — Aegelis, Apr 01 '14 at 16:04
It's not just that the original is correct. It's that this "new" alternate is horribly, horribly, wrong. It's not okay to use string concatenation to put data into a query like that. — Joel Coehoorn, Apr 01 '14 at 18:47

Joel Coehoorn · Answer 1 · 2014-04-01T19:00:12.263

Do NOT do this. EVER. It leaves you vulnerable to a very serious security issue known as Sql Injection.

If you want a different way to add a parameter, I can give you one that will actually improve your code:

Dim updateStatement As String =
    "UPDATE Customers SET " &
    "Name = @NewName, "
    "WHERE Name = @OldName "

'You should also create your connection this way
Using updateCommand As New OleDbCommand(updateStatement, connection)
    'I have to guess at your DB types and lengths
    updateCommand.Parameters.Add("@NewName", OleDbType.VarChar, 50).Value = newCustomer.Name
    updateCommand.Parameters.Add("@OldName", OleDbType.VarChar, 50).Value = oldCustomer.Name
    updateCommand.ExecuteNonQuery()
End Using

There are a certain issues you can run into when using .AddWithValue(), because it forces .Net to guess what the database type of your parameter will be. Sometimes, .Net will guess wrong. Even when it guesses wrong, things usually still work most of the time, but what can happen is that performance will suffer dramatically, because the type mismatch forces per-row conversions or breaks the use of an index.

score 1 · Answer 2 · answered Apr 01 '14 at 09:56

I'm not going to give you an answer on how to concatenate queries (i.e. concatenate literal values into the query) correctly because concatenating queries is not the correct way.

The correct way is the way you originally used: using parameters.

If you put literal values into your queries, you have the following problems:

your code is vulnerable to SQL injection. That reason is enough to never recommend it.
you may have problems with types conversion, e.g. for dates, times, numbers as you pass values as strings which have to be converted into proper data types by the database system.

score 0 · Answer 3 · edited May 23 '17 at 12:22

0

You're missing some well-placed string concatenation operators and spaces to get the syntax right:

Dim SQL as String = "UPDATE Customers SET " & "Name = '" & newCustomer.Name & "'"

BUT, and perhaps you might already notice the security issue with this, if I set Customer.Name to "maliciousUser'; DROP TABLE Customers; -- " what could happen? Different is not necessarily better.

Check this out: From what do sql parameters protect you?

edited May 23 '17 at 12:22

Community

1
1

answered Apr 01 '14 at 09:55

Oran D. Lord

697
1
9
23

I know it's not better. I want to keep it the same, however, I am supposed to learn different ways of doing it. How do I set the Customer.Name so that the program knows where its pulling it from? – Aegelis Apr 01 '14 at 16:06
@Aegelis Take a look at it now. You have to reference whatever variable is holding your data. It looks like you were using `newCustomer.Name` before, but you'll need to reference the variable that's holding your data. If you are still not underst anding, I would suggest that your teacher may be a better resource, since while we can point out code errors and suggest corrections, they can help you understand the syntax and concepts like objects with member variables. (We can help too, but a discussion with the teacher may be more enlightening for you.) – Oran D. Lord Apr 01 '14 at 18:58

score 0 · Answer 4 · answered Sep 13 '15 at 20:32

 ' Add CustomerID parameter for WHERE clause.

    command.Parameters.Add("@ID", SqlDbType.Int)
    command.Parameters("@ID").Value = customerID


    ' Use AddWithValue to assign Demographics. 
    ' SQL Server will implicitly convert strings into XML.

 command.Parameters.AddWithValue("@demographics", demoXml)

CHeck this one please

AddWithValue to SQL Statements? VB.Net

4 Answers4