0

I am trying to retrieve a dataset from a database using a sql string in vb.net.

Code:

Dim sSql As String = "Select * FROM mytable where id = " + propID
Dim ds As DataSet = New DataSet()

Try
ds = coApp.ConnectionManager.LoadSQLToDs(sSql)
Catch ex As Exception

The propID variable is a value with '/' in it, like '1/83/42'. The exception message is: Incorrect syntax near '/'. How can I pass this value in my sql string?

Sora
  • 50
  • 4

1 Answers1

-2

Does the value of propID already contain quotes in it? If not simply quoting it should resolve the issue:

Dim sSql As String = "Select * FROM mytable where id = '" + propID + "'"

If you were to print the variable sSql in your code You'd get the SQL:

Select * FROM mytable where id = 1/83/42

which is invalid SQL.

blitz1616
  • 341
  • 3
  • 12
  • 6
    NO! This isn't a great idea. We don't know where the value of `propID` is coming from, and that statement you have there is wide open for SQL Injection. **Parametrise** your SQL or use a Stored Procedure. – Thom A Jan 24 '18 at 21:28
  • So what?The OP just wanted to get the cause to the error and not SQL best practices, obviously a beginner mistake and not a production system, that answer is fine. – Y.S Jan 24 '18 at 21:31
  • @larnu I agree that this is a bad idea but this is the solution to the asked question. We don't know where `propID` is coming from but the asker does. If it is not from a user there is nothing wrong with this code, there are definitely better ways to do it but that does not make this open for SQL Injection. – blitz1616 Jan 24 '18 at 21:33
  • 4
    So what? So **everything**. Providing an answer that is wide open to SQL injection is not an answer. Much like using Duck Tape to seal a hole in your fuel tank of your car isn't. It's an awful idea, and a possible disaster waiting to happen. – Thom A Jan 24 '18 at 21:33
  • 5
    "How can I pass this value in my sql string?" was the question, not "Why is this occuring". Parameterization is the best answer. End of story. – Jacob Barnes Jan 24 '18 at 21:33
  • Best answer would be to tell a beginner how quotes / strings / etc comes into play when passing it via code string concatenations, this is a beginner here. SQL binding parameters are indeed the best / safest / performance oriented answer, but not relevant in this scenario to my opinion. I think things needs to be kept simple. There's plenty of reading material and other SO posts discussing this when OP gets to the next level.That's why I think this answer is good, a reference to best practices at the bottom would make it great. @ja – Y.S Jan 24 '18 at 21:37
  • Guess S/O disagrees as this is now closed and pointing to a "good" answer. I get your point though. – Jacob Barnes Jan 24 '18 at 21:40
  • @JacobBarnes * LarsTech [Gold vb.net] disagrees :) – djv Jan 24 '18 at 22:37