Mysql Soundex in VB.NET format

Question

I have these lines of codes and Im trying to look into my database for soundex results but apparently it's not returning any result.

con.Open()

    Try
        Dim query As String
        query = "SELECT * FROM table_name WHERE column_name LIKE CONCAT('" & "%" & "',SOUNDEX('" & input & "'),'" & "%" & "')"
        cmd = New MySqlCommand(query, con)
        adapter.SelectCommand = cmd
        reader = cmd.ExecuteReader
        Dim list As New ListViewItem
        While reader.Read()
            list = ListView1.Items.Add(dr(1).ToString())
            list.SubItems.Add(dr(2).ToString())
        End While
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

    con.Close()

It doesn't even throw exception on MessageBox.

To ensure there is data returned from my database before i did the query above, i did select * from table_name.

Thanks!

This will be crazy-vulnerable to sql injection attacks. It's practically begging to get hacked. Never ever **EVER** use string concatenation to put input in an sql query. — Joel Coehoorn, Feb 05 '15 at 19:51
I don't think this is not enough context. For example: have you checked whether you have results in your db by connecting to the db directly and ensuring you have records? — zealoushacker, Feb 05 '15 at 19:52
Could you provide sample data, expected output, and the value of the `input` variable? Your query looks solid-ish (except for the gaping SQL injection vulnerability) so it's probably not a syntax error, and based on the posted code all anyone can do is verify syntax. — Ed Gibbs, Feb 05 '15 at 19:55
@zealoushacker i did, i also did the simple test if there will be a result like `select * from table_name` command alone. — slverstone, Feb 05 '15 at 19:56
@EdGibbs For example, i have strings in my `column_name` "extreme", "extravagance", "programming", "sample" and the `input` is "extra". I want the query to return "extreme" and "extravagance".. — slverstone, Feb 05 '15 at 20:00
@JoelCoehoorn Thank you for the advice sir, I will note that. — slverstone, Feb 05 '15 at 20:02

score 1 · Accepted Answer · answered Feb 05 '15 at 21:15

I'll prefix this by saying that I am not at all familiar with MySQL, but the Soundex algorithm typically returns a "code" that will be the same for similar sounding words. e.g.

SOUNDEX('Smith') = 'S530'
SOUNDEX('Smythe') = 'S530'

So, in order to find records where the column_name value sounds like the input value you would want something like this (heed the warnings about SQL injection issues and don't use string concatenation as shown below!):

query = "SELECT * FROM table_name WHERE SOUNDEX(column_name) = SOUNDEX('" & input & "')"

Mysql Soundex in VB.NET format

1 Answers1