CODEWITHSUNDEEP
sqlvbaexcel
Carlos80
Carlos80

Reputation: 433

Excel cell value to SQL including special charatcers

I'm trying to write information from an excel spread sheet to a table in SQL. The code is working fine, however I have stumbled across special character issue. I have a column in my spread sheet for Notes, which are then transferred to the SQL table. I found a comment with a comma and the code failed.

Is there a way of changing the SQL table to handle this or amending the VBA code?

Code below:

stSQL = "INSERT INTO Notes " & _
                    "(ID, Category, Ref, Client, Notes, Date_Loaded, Latest) " & _
                    " VALUES (" & _
                    "1, " & _
                    "4, " & _
                    "'" & Sheets("Clients").Range("N" & i).Offset(0, -12).Value & "', " & _
                    "'" & Sheets("Clients").Range("N" & i).Offset(0, -11).Value & "', " & _
                    "'" & Sheets("Clients").Range("N" & i).Value & "', " & _
                    "'" & Date & "', " & _
                    "1)"

Thanks in advance

Upvotes: 1

Views: 46

Answers (1)

Vityata
Vityata

Reputation: 43593

In general SQL Injection is something that you should be well aware of, if you are coding anything with SQL.

This was a big security thing about 10 years ago in all languages. Nowadays, almost all programming languages are automatically protected by it.

Even VBA.

In general, you need to write something like this (code DbFormus):

Dim cmd            As ADODB.Command
Dim prm1           As ADODB.Parameter
Dim prm2           As ADODB.Parameter
Dim prm3           As ADODB.Parameter
Dim strSql         As String

strSql = "UPDATE justtesting SET myTest = ?, myDate = ? WHERE id = ?"

Set cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = CurrentProject.Connection

    .CommandText = strSql
    .CommandType = adCmdText

    Set prm1 = .CreateParameter("mytext", adVarWChar, adParamInput, 35)
    .Parameters.Append prm1
    prm1.Value = "as""d''##'fasdf"

    Set prm2 = .CreateParameter("mytimestamp", adDate, adParamInput)
    .Parameters.Append prm2
    prm2.Value = Now

    Set prm3 = .CreateParameter("mylong", adInteger, adParamInput)
    .Parameters.Append prm3
    prm3.Value = 42

    .Execute , , adExecuteNoRecords
End With
Set cmd = Nothing

Take a look at the question marks in the code. Even if you try SQL injection on them, ADODB knows what to do, to go around it and to protect your data.

You can find more examples about it if you google it like this: https://www.google.com/search?q=avoid+sql+injection+vba&oq=avoid+sql+injection+vba&aqs=chrome..69i57.3193j0j4&sourceid=chrome&ie=UTF-8

In general, reading SQL the way you are doing it is a HUGE security issue. The only reason to write like this is if you are the only person using the Application. In general anyone with almost no skills can drop your database or read the values there. Or do other painful stuff.

Upvotes: 1

Related Questions