在sql insert语句中使用单引号忽略逗号 [英] Ignore Comma with Single quote in sql insert statement

问题描述

我有以下价值



strNm =''MyData''，''MySinger''





插入或更新时给我错误



我的查询是

insert into表值（''+ strNm +''）

i have value like following

strNm="''MyData'',''MySinger''"


while inserting or updating it give me error

my query is
insert into Table values(''" + strNm + "'')

推荐答案

不要这样做！

不要连接用于构建SQL命令的字符串。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。你的问题会在同一时间神奇地消失！

并且 总是 在插入记录时列出你的列名！

Don''t do it like that!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. Your problem will magically disappear at the same time!
And always list your column names when inserting records!

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO myTable (myColumn1) VALUES (@C1)", con))
        {
        com.Parameters.AddWithValue("@C1", strNm);
        com.ExecuteNonQuery();
        }
    }

Using con As New SqlConnection(strConnect)
    con.Open()
    Using com As New SqlCommand("INSERT INTO myTable (myColumn1) VALUES (@C1)", con)
        com.Parameters.AddWithValue("@C1", strNm)
        com.ExecuteNonQuery()
    End Using
End Using

检查以下脚本，

Hi ,

Check the following Script,

-- If Object Exists then Drop
IF OBJECT_ID('split') IS NOT NULL DROP FUNCTION dbo.split
IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
GO
-- Create Split Function
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
RETURN
END
GO
-- Create Temp Table 
CREATE TABLE #Test(Name VARCHAR(100))

DECLARE @DataString VARCHAR(1000)='MyData,MySinger'

INSERT INTO #Test(Name)
SELECT Val FROM dbo.split(@DataString, ',')

-- Actual Output
SELECT Name FROM #Test

问候，

GVPrabu

Regards,
GVPrabu

您好，



尝试此查询

strNm =（SELECT''MyData''UNION ALL SELECT''MySinger''）

Hi,

try this query
strNm="(SELECT ''MyData'' UNION ALL SELECT ''MySinger'')"

insert into Table(column1) + strNm +   

您使用单引号两次。一个在值赋值，另一个在查询。删除查询中的单引号，它将起作用。



希望它有所帮助。

you are using single quotes twice. one at value assignment and other at query. remove the single quote at query, it will work.

hope it helps.

这篇关于在sql insert语句中使用单引号忽略逗号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！