String from DB is destroying my query

[blacksaibot]

Some one built a terrible database and it's now up to me to put the data they captured in a new database that's been designed.
I am querying a table and pulling out its text and sending that text into the new database via a query:

        For Each record In listOfRecords

            queryStr = "SELECT * FROM t_user_ldr WHERE recnum=" & record

            con.Open()

            cmd = New OleDbCommand(queryStr, con)
            Using reader As OleDbDataReader = cmd.ExecuteReader()
                While reader.Read()

                    recnum = reader.GetValue(0)
                    qryStart = "INSERT INTO tbl_user_ldr_responses VALUES(" & recnum & ", "

                    Dim i As Integer = 4
                    Dim j As Integer = 11

                    For i = 4 To 42
                        insert(qryStart & i & ", '" & reader.GetValue(j) & "', '" & reader.GetValue(j + 1) & "')")
                        j = j + 2
                    Next

                End While
            End Using

            con.Close()

        Next

Problem is... that if the text I'm grabbing contains a single quote, the SQL or whatever things that's the end of the string. How can I change my code that no special characters ruins my query???



Thanks!

 

[Herman]

The problem you are having is called SQL injection, in your case you stumbled upon it by accident, others exploit this to run nasty queries... Your best bet is to replace your string query to a parametrized query.

 

[jmcilhinney]

The error you're experiencing is a actually a syntax error, not SQL injection. They are both possible issues that can arise from using string concatenation to build SQL statements. A syntax error will cause an exception and possibly an application crash, which is bad. SQL injection could potentially delete every record in your database, which is probably worse. Herman is quite right that you should be using parameters to insert values into your SQL code though. To that end, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.