如何在SQL中插入特殊字符 [英] How to Insert Special Characters Into SQL

问题描述

我正在尝试将文本发送到我的数据库，该数据库可能包含特殊字符，例如O'Brian这样的名称。或者像我不知道位置在哪里这样的短语。我一直收到错误，说'有问题。

如何确保特殊字符作为纯文本进入数据库。

 Imports system.Data 
 Imports System.Data.SqlCLient 
 
 Protected Sub ButtonSubmitYourName_Click（sender as Object，e As EventArgs）处理ButtonSubmitYourName .Click 
 Dim Conn As New SqlClient.SqlConnection 
 Conn.ConnectionString =Data Source = <   DatabaseServer  > ;初始目录= <  数据库 > ;用户ID = <   user  > ;密码= <  密码 > 
 Dim命令As SqlCommand 
 Dim sqlString，LastName，FirstName，YourCredentials As String 
 
 LastName = TextboxLastName.Text 
 FirstName = TextBoxFirstName.Text 
 YourCredentials = TextBoxYourCredentials.Text 
 
 
 Conn = New SqlConnection（Data Source = <  < span class =code-leadattribute> DatabaseServer  > ;初始目录= <  数据库 >  ;用户ID = <  用户 > ;密码= <  密码 > ）
 
 Conn.Open（）
 sqlString =INSERT INTO <  表 > （LastName，FirstName，YourCredentials）VALUES（'+ LastName +'，'+ FirstName +'，'+ YourCredentials +'）
 command = New SqlCommand（sqlString，Conn）
 command.ExecuteNonQuery（）
 Conn.Close（）
 
 Response.Redirect（ YourName.aspx）
 
 End Sub 

解决方案

这是因为你是连接用于形成SQL命令的字符串 - 特殊字符是您遇到的最少问题。当你这样做时，你可以在SQL INjection中保持开放状态，用户可以在数据库中执行任何他喜欢的操作，只需键入文本框 - 包括删除行或删除所有表。始终使用参数化查询：

 sqlString =   INSERT INTO< Table>（LastName，FirstName，YourCredentials）VALUES（@ LN，@ FN，@ YC） 
 command = 新 SqlCommand（sqlString，Conn）
 command.Parameters.AddWithValue（  @ LN，LastName ）
 command.Parameters.AddWithValue（  @ FN，FirstName）
 command.Parameters.AddWithValue（  @ YC，YourCredentials）
 command.ExecuteNonQuery（） 

I am trying to send text to my database that may contain special characters such as a name like "O'Brian". Or a phrase like "I don't know where the location is". I keep getting an error saying that there is a problem with "'".
How do you make sure that special characters get into the database as pure text.

Imports system.Data
Imports System.Data.SqlCLient

 Protected Sub ButtonSubmitYourName_Click(sender As Object, e As EventArgs) Handles ButtonSubmitYourName.Click
        Dim Conn As New SqlClient.SqlConnection
        Conn.ConnectionString = "Data Source=<DatabaseServer>; Initial Catalog=<Database>;user ID=<user>;Password=<password>"
        Dim command As SqlCommand
        Dim sqlString, LastName, FirstName, YourCredentials As String

        LastName = TextboxLastName.Text
        FirstName = TextBoxFirstName.Text
    YourCredentials = TextBoxYourCredentials.Text


        Conn = New SqlConnection("Data Source=<DatabaseServer>; Initial Catalog=<Database>;user ID=<user>;Password=<password>")

        Conn.Open()
        sqlString = "INSERT INTO <Table> (LastName, FirstName, YourCredentials)VALUES('" + LastName + "','" + FirstName + "','" + YourCredentials + "')"
        command = New SqlCommand(sqlString, Conn)
        command.ExecuteNonQuery()
        Conn.Close()

        Response.Redirect("YourName.aspx")

    End Sub

解决方案

It's because you are concatenating strings to form your SQL command - and "special characters" are the least of your problems. When you do this, you leave yourself wide open fro SQL INjection, where the user can do anything he likes to your database, just by typing in the text boxes - including deleting rows or dropping all your tables. Use parameterized queries at all times:

sqlString = "INSERT INTO <Table> (LastName, FirstName, YourCredentials)VALUES(@LN, @FN, @YC)"
command = New SqlCommand(sqlString, Conn)
command.Parameters.AddWithValue("@LN", LastName)
command.Parameters.AddWithValue("@FN", FirstName)
command.Parameters.AddWithValue("@YC", YourCredentials)
command.ExecuteNonQuery()

这篇关于如何在SQL中插入特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！