我有一些问题要问我将对此错误做些什么，没有给出一个或多个必需参数的值 [英] I have something to ask what will I do to this error, No value given for one or more required parameters

问题描述

Imports System.Data.OleDb

Public Class Form1
    Dim cnn As New OleDb.OleDbConnection


    Private Sub RefreshData()
        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        Dim da As New OleDb.OleDbDataAdapter(" SELECT login as [login], password as [password] FROM login ", cnn)

        Dim dt As New DataTable

        da.Fill(dt)

        dgvData.DataSource = dt
        cnn.Close()
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim cmd As New OleDb.OleDbCommand

        If Not cnn.State = ConnectionState.Open Then
            cnn.Open()
        End If

        cmd.Connection = cnn
        cmd.CommandText = " INSERT INTO login ([login], [password]) " & _
                        " VALUES(" & txtLog.Text & "," & txtPass.Text & ")"
        cmd.ExecuteNonQuery() ====> this is where the error occur

        RefreshData()

        cnn.Close()
   
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cnn = New OleDb.OleDbConnection
        Dim sql_query As String
        sql_query = "SELECT * FROM login"

        cnn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source = " & Application.StartupPath & "\Church.mdb"
    End Sub

End Class

推荐答案

您的代码易受影响 SQL Injection [ ^ ]。br />


从不使用字符串连接来构建SQL查询。总是使用参数化查询：

Your code is susceptible to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query instead:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
    Dim cmd As New OleDb.OleDbCommand

    If Not cnn.State = ConnectionState.Open Then
        cnn.Open()
    End If

    cmd.Connection = cnn
    cmd.CommandText = "INSERT INTO login ([login], [password]) VALUES (?, ?)"
    cmd.Parameters.AddWithValue("p0", txtLog.Text)
    cmd.Parameters.AddWithValue("p1", txtPass.Text)

    cmd.ExecuteNonQuery()

    RefreshData()

    cnn.Close()
End Sub

一旦修复了代码中存在的漏洞，您需要查看密码存储。您目前正在以纯文本形式存储密码，这是一个糟糕的主意。你应该只存储密码的盐渍哈希。



Salted Password Hashing - 正确行事 [ ^ ]

您好，请将此作为您的基本示例，您应该效仿。尽管您选择了解决方案（解决方案1）;工作。您不通过参数来妥协整个系统。我强调您从安全角度更改解决方案。

Hello, let this be a basic example for you, which you should follow suit. All though the solution you choose (Solution 1); worked. You are compromising your whole system by not using parameters. I stress that you change your solution from a security standpoint.

'Please be sure to declare your cnn as your connection.         
            
                Query = "INSERT INTO login ([login], [password]) VALUES (@User, @Pass)"

'Do not concatenate queries. Use Parameters.

'Connect your query command to the connection.

                Dim QueryCom As New OleDb.OleDbCommand(Query, cnn)
                QueryCom.Parameters.AddWithValue("@username", txtLog.Text)

'@username and @guid is a placeholder for the declared declarations UserName and iUserGuid.
                QueryCom.Parameters.AddWithValue("@Pass", txtPass.Text)

'Check if the connection is open or not, if its closed, open it...
'Don't assume something, check it first.

                If cnn.State = ConnectionState.Closed Then
                    cnn.Open()
                End If
                QueryCom.ExecuteNonQuery()

                cnn.Close() 'Close the connection 
                cnn.Dispose() 'Dispose of it and its resources, don't just close it. 

'You should also wrap your communication statements in Try Catch Blocks to handle exceptions which may arise.             

第一眼看...你必须添加一个'字符字符串值。

On the first look... You have to add a ' character around the string values.

INSERT INTO login ([login], [password]) 
VALUES('stringValue','stringValue')

如果是数字值，你必须使用它：

In case of numeric values, you have to use this:

INSERT INTO login ([NumericField1], [NumericField2]) 
VALUES(22,0)

如果是datetime值，你必须使用它：

In case of datetime values, you have to use this:

INSERT INTO login ([DateTimeField1], [DateTimeField2]) 
VALUES(#MM/dd/yyyy#,#MM/dd/yyyy#)

最后，我建议使用带有命名参数的查询 [ ^ ]：

Finally, i suggest to use queries with named parameters[^]:

PARAMETERS [slogin] CHAR, [sPasswd] CHAR;
INSERT INTO [login] ([login],[password])
VALUES([slogin], sPassword)

使用参数 [ ^ ]是非常好的做法。您可以通过 OleDbParameterCollection.AddWithValue添加它方法 [ ^ ]



最后，我需要警告你：不要使用保留字词 [ ^ ]！这可能是几个麻烦的原因。


干杯！

Using parameters[^] is very good practice. You can add it via OleDbParameterCollection.AddWithValue Method[^]

Finally, i need to warn you: DO NOT use reserverd words[^]! It might be the reason of several troubles.

Cheers!

这篇关于我有一些问题要问我将对此错误做些什么，没有给出一个或多个必需参数的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！