0

I'm trying to execute this SQL query on my database with VB.NET:

Dim sql As New MySqlCommand("SELECT * FROM `personer` WHERE epost LIKE %" _
            & txbEpost.Text, tilkobling)

The problem is that it keeps getting an error, and I have no idea why. It keeps saying that there's a syntax error near 'gmail.com' (given I've written gmail.com in txbEpost.Text). But running the exact same query in my web interface MySQL yields the expected results.

What am I doing wrong?

gloriousCatnip
  • 411
  • 1
  • 6
  • 18

3 Answers3

3

The first problem in your code is the missing quote around a string value

"... WHERE epost LIKE '%" & txbEpost.Text &"'"

However, this query should be done using parameters to avoid sql injection and other parsing problems (what if the textbox contains a single quote?)

Dim sql As New MySqlCommand("SELECT * FROM `personer` WHERE epost LIKE @epost"
sql.Parameters.Add("@epost", MySqlDbType.VarChar).Value = "%" & txbEpost.Text
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Hm, I tried that, but now when I try to fill my DataTable it says that the input data is formatted incorrectly. This is the code: http://pastebin.com/kaf78AWH It throws an error when txbEpost.Text equals "gmail.com" – gloriousCatnip Mar 03 '17 at 09:08
  • @Steve would it be worth placing `%` here `LIKE + '%' + @epost`? – Bugs Mar 03 '17 at 09:12
  • When I replaced the `MySqlDbType.VarChar` with `"%" & txbEpost.Text`, and removed `.Value` it worked wonders! Thanks! – gloriousCatnip Mar 03 '17 at 09:20
  • @gloriousCatnip Uhm, It is not clear what you have changed. In the code above the Add method returns a parameter whose value should be set to the string you want to use in the LIKE expression (minus the quotes). Can you post your actual line? – Steve Mar 03 '17 at 09:37
  • 1
    @Bugs Yes, I think it is possible to use also that syntax, but when possible I try to avoid any concatenation in the command text. Perhaps it is just an habit. – Steve Mar 03 '17 at 09:38
  • @Steve I have changed it because it didn't work, but now this line works: `sql.Parameters.AddWithValue("@epost", "%" & txbEpost.Text)` I used this before in the example above: `sql.Parameters.AddWithValue("@epost", MySqlDbType.VarChar).Value = "%" & txbEpost.Text` – gloriousCatnip Mar 03 '17 at 09:46
  • The second parameter of AddWithValue is the value for the parameter, in my example above I use Add to be more precise on the DataType of the parameter. See [Can we stop using AddWithValue already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) but in case of strings there is no big difference (a part performances with optimizing engines that keep track of the string passed) – Steve Mar 03 '17 at 10:10
-1

hi there think that should work

MySqlCommand("SELECT * FROMpersonerWHERE epost LIKE '%" _
            & txbEpost.Text & "'", tilkobling)

because of your sql string is missing the "'". The quote is necessary

Roberto Benazzato
  • 365
  • 4
  • 16
  • This is open to SQL injection. When it comes to answering these types of questions _always_ suggest parameters and get into the habit of doing it yourself. Steve's answer shows how it should be done. – Bugs Mar 03 '17 at 09:02
-1

how about this:

MySqlCommand("SELECT * FROMpersonerWHERE epost LIKE '%" & txbEpost.Text & "%'", tilkobling)

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
  • This is open to SQL injection. When it comes to answering these types of questions _always_ suggest parameters and get into the habit of doing it yourself. Steve's answer shows how it should be done – Bugs Mar 03 '17 at 09:01
  • 1
    Thanks @Bugs for the heads up,, then show your answer here if you have. – Vijunav Vastivch Mar 03 '17 at 09:04
  • Not a problem. As for answering, normally I would however I don't need to in this case as Steve has done a nice job of it already. It's how I would do it too. It would be pointless providing a duplicate answer. – Bugs Mar 03 '17 at 09:06
  • how about if use like this after like condition str.Format("{0}{1}{2}", "'%", txbEpost.Text, "%'") – Vijunav Vastivch Mar 03 '17 at 09:13
  • It's the concatenating strings that can cause SQL injection. Have a look at [this](http://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). It may prove useful. – Bugs Mar 03 '17 at 09:14