[vb.net ]SQLite geparameteriseerde entry gaat fout

[Damic]

Voor dat in te voeren in een sqlite db gebruik ik geparameteriseerde entry's, nu wil het lukken dat het niet lukt. Als ik de insert doe dan krijg ik de fout melding dat ie @table niet kan vinden.

Dit is wat er in het debug scherm komt

code:

----------------
@table = [tblUser] (String)
@UserName = [z603311] (String)
@colums = [UserName] (String)
@values = [@UserName] (String)

INSERT INTO @table (@colums)VALUES(@values);
----------------

SQLite error (1): near "@table": syntax error
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll

Als ik dan uit de functie gaat dan komt er het volgende bij (deze insert is via een andere functie)

code:

Exception thrown: 'System.Exception' in Telemeter Indicator.exe

----------------
@uname = [z725831] (String)
@message = [IMPORT_DB::ERR: SQL logic error or missing database
near "@table": syntax error] (String)

select last_insert_rowid();
Rows Updated: 29054
----------------

De functie die ik gebruik: [code=vb] 'insert data, returns last inserted id
Public Function Insert(table As String, data As Dictionary(Of String, Object)) As Integer
Insert = -1
Try
If data.Count >= 1 Then
Using cmd As New SQLiteCommand("INSERT INTO @table (@colums)VALUES(@values);")
cmd.Parameters.AddWithValue("@table", table)
Dim columns As String = ""
Dim values As String = ""
For Each val As KeyValuePair(Of String, Object) In data
columns &= val.Key & ","
values &= "@" & val.Key & ","
cmd.Parameters.AddWithValue("@" & val.Key, val.Value)
Next
columns = columns.Substring(0, columns.Length - 1)
values = values.Substring(0, values.Length - 1)

cmd.Parameters.AddWithValue("@colums", columns)
cmd.Parameters.AddWithValue("@values", values)
columns = Nothing
values = Nothing

If bdebug Then SqlString(cmd)

Using conn As SQLiteConnection = New SQLiteConnection(db)
conn.OpenAsync()
Using trans As SQLiteTransaction = conn.BeginTransaction
cmd.Connection = conn
cmd.ExecuteNonQuery()
cmd.CommandText = "select last_insert_rowid();"
Insert = CInt(cmd.ExecuteScalar)
trans.Commit()
End Using
End Using
cmd.Parameters.Clear()
End Using
End If
Catch e As Exception
Throw New Exception(e.Message)
End Try
End Function[/code=vb]

Wat gaat er fout, is dat om dat er nog een @ overblijft of?

[sig69]

Je gebruikt parameters verkeerd. Je hebt een parameter voor columns en values (en table) en daarnaast voor elke waarde nog een parameter. En nu moet SqlLite daar magisch een correcte query van bakken?
Je colums, values en table parameters moeten geen parameters zijn, je moet hiermee een correcte query string opbouwen. Met een replace ben je er al bijna volgens mij.

[Damic]

Dus die @values moet ik via een replace doen, maar is dat dan nog wel veilig?

[sig69]

Ja want daar staan alleen je kolomnamen als parameters in (er vanuit gaande dat deze niet uit user input komen).

[ Voor 31% gewijzigd door sig69 op 04-06-2017 14:11 ]

[Damic]

Euhm dit kan user input zijn dus

Anyway ik heb vervangen cmd.Parameters.AddWithValue("@values", values) met cmd.CommandText = cmd.CommandText.Replace("@values", values) maar het blijft niet goed

code:

----------------
@table = [tblUser] (String)
@UserName = [z603311] (String)
@colums = [UserName] (String)

INSERT INTO @table (@colums)VALUES(@UserName);
----------------

SQLite error (1): near "@table": syntax error
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll
Exception thrown: 'System.Exception' in Telemeter Indicator.exe

----------------
@uname = [z725831] (String)
@message = [IMPORT_DB::ERR: SQL logic error or missing database near "@table": syntax error] (String)

select last_insert_rowid();
Rows Updated: 29081
----------------

[sig69]

Dit is nu dus je query?
INSERT INTO @table (@colums)VALUES(@UserName)
Je moet de table en columns variabele ook nog replacen.

[Damic]

Dat moet volgens mij niet met PARAMETERS, want dit gaat wel goed:

Visual Basic:

            If UpdateLogEntry = -1 Then
                sql1 = "INSERT INTO tblLog (UTCtime, UserName, LogType, Message) values (" & ToUnix(Date.UtcNow) & ", @uname, " & logtype & ", @message);"
            Else
                sql1 = "UPDATE tblLog SET Message=@message, LogType=" & logtype & " where LogId=" & UpdateLogEntry & ";"
                sInput = LastLogMessage & " >> " & sInput
            End If
            Using cmd As New SQLiteCommand(sql1)
                cmd.Parameters.AddWithValue("@uname", sLastUser)
                cmd.Parameters.AddWithValue("@message", sInput.Replace(Environment.NewLine, " "))
                Add2Log = dblog.Execute(cmd)
                LastLogEntry = Add2Log
                LastLogMessage = sInput
                cmd.Parameters.Clear()
            End Using

[sig69]

Ja dat is precies wat ik zeg. Je moet een valide sql string opbouwen. Je tabel en je kolommen zet je niet in parameters.

[Damic]

Mmmh nu zien ik het parameters wil eigenlijk zeggen ValuesOnly

is er dan wel een veilige manier om dat te doen, want een simpele replace is volgens mij niet veilig

[sig69]

Tsja als het allemaal user input is.. Ben je een table editor aan het bouwen ofzo? Waarom heeft de gebruiker kennis nodig van je database structuur?

[Damic]

Nee gebruikers moeten dat niet weten, ik wil het gewoon zo veilig mogelijk maken

[Dido]

Damic schreef op zondag 4 juni 2017 @ 15:01:
Nee gebruikers moeten dat niet weten, ik wil het gewoon zo veilig mogelijk maken

Je maakt niets veiliger door een tabel of kolomnamen te parametriseren, want die horen niet uit de userinput te komen(!!!!!)

Maak dus aparte methodes voor aparte operaties op aparte tabellen.

Het kan wel generiek, maar dat wordt lastig met een wisselend aantal kolommen.

Maak van

code:

Using cmd As New SQLiteCommand("INSERT INTO @table (@colums)VALUES(@values);")

Gewoon

code:

Using cmd As New SQLiteCommand("INSERT INTO tblUser (UserName)VALUES(@UserName);")

Pas je function signature aan naar

code:

Public Function InsertUser(userName As String) As Integer

En je bent er - voor die ene tabel / functie.

Ja, maar dan moet ik voor elke operatie een aparte functie schrijven?

Zoals gezegd, je kunt een en ander generaliseren. Je kunt wel een tabelnaam meegeven, en een variabnel aantal kolomnamen, maar dat mag NOOIT uit de userinput komen, want de user hoort de namen van jouw tabellen en kolommen niet te weten.

Waarom denk je dat er zoveel ORM-frameworks zijn die dit soort dingen voor je genereren?

[ Voor 5% gewijzigd door Dido op 04-06-2017 15:20 ]

[sig69]

Damic schreef op zondag 4 juni 2017 @ 14:14:
Euhm dit kan user input zijn dus

Op basis hiervan dacht ik van wel namelijk. Als de gebruiker de kolomnamen hoeft in te geven (dus deze worden door je applicatie bepaald), dan is er verder niets mis mee.

[Damic]

@Dido ha juist aparte functies aanmaken die dan die andere functie aanroept waarom dacht ik daar nu niet aan.

@sig69 ja dat is waar, maar da kan ik afvangen door (zoals dido al zei) een aparte functie.

Kees klozed

[Damic]

Even een kick geven vermits ik het de vorige keer anders had opgelost, maar nu met een ander programma kwam ik tegen hetzelfde probleem en heb ik het al een beetje opgelost.

@mods: je mag dit afsplitsen maar moet niet

Functie aangepast zodoende dat het werkt

Visual Basic .NET:

Public Function InsertOrReplace(Tablename As String, Columns As String, data As Dictionary(Of Integer, List(Of String))) As Integer
        InsertOrReplace = -1
        If data.Count < 1 Then Exit Function
        Try
            Using cmd As New SQLiteCommand("INSERT OR REPLACE INTO " & Tablename & " (" & Columns & ") VALUES (@vars);", New SQLiteConnection(dbConnection))
                'PrepareValues(Columns, data, cmd)
                Dim values As String = "", key As String = ""
                Dim s() = Columns.Split(New Char() {","c})
                For Each val As KeyValuePair(Of Integer, List(Of String)) In data
                    If s.Count = val.Value.Count Then
                        values &= "("
                        For i = 0 To val.Value.Count - 1
                            key = val.Key & "." & i
                            values &= "@" & key & ","
                            cmd.Parameters.AddWithValue("@" & key, val.Value(i))
                        Next i
                        values = values.Substring(0, values.Length - 1) & "),"
                    End If
                Next
                'cmd.Parameters.AddWithValue("(@vars)", values.Substring(0, values.Length - 1))
cmd.CommandText = cmd.CommandText.Replace("(@vars)", values.Substring(0, values.Length - 1))
            
                values = Nothing
                s = Nothing

                cmd.Connection.OpenAsync()
                'cmd.Connection.Open()
                Using trans As SQLiteTransaction = cmd.Connection.BeginTransaction
                    InsertOrReplace = cmd.ExecuteNonQuery()
                    trans.Commit()
                End Using
                If bdebug Then SqlString(cmd, InsertOrReplace)
                cmd.Parameters.Clear()
                cmd.Connection.Close()
            End Using

        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
    End Function

Krijg ik leuk het volgende terug als ik het zo aanroep:
[code]dbdata.InsertOrReplace("tblStations", "'StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate'", dicstation)[/code]
Ja de dat zit mooi in de parameters lijst
De error:
[code]SQLite error (1): near "@vars": syntax error in "INSERT OR REPLACE INTO tblStations ('StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate') VALUES @vars;"[/code]
en als ik @vars tussen () zet krijg ik het volgende
[code]SQLite error (1): 1 values for 9 columns in "INSERT OR REPLACE INTO tblStations ('StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate') VALUES (@vars);"
Exception thrown: 'System.Data.SQLite.SQLiteException' in System.Data.SQLite.dll
[/code]

[ Voor 1% gewijzigd door Damic op 27-07-2019 08:19 . Reden: MIljaar, ik moest een replace doen in de commandtext ]