C#数据库操作特殊字符单引号三种处理方式

最新推荐文章于 2024-05-01 14:22:29 发布

iteye_14608

最新推荐文章于 2024-05-01 14:22:29 发布

阅读量1.6k

点赞数

文章标签：数据库 c# java

本文介绍三种处理SQL语句中特殊字符的方法：转义字符、SqlDataAdapter及参数化SQL语句。通过具体代码示例展示了如何避免SQL注入风险。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方法一：转义字符

使用单引号作为转义字符，即连续使用两个单引号。

select * from jq_jjjl where bt like '%女子''%'

上述代码会匹配jq_jjjl表中所有bt字段包含女子'的记录。（注意单引号）

方法二：SqlDataAdapter

string constr = "Server=" + DBConfig.DBAPP_IP + ";user id=" + DBConfig.DBAPP_USER + ";password=" + DBConfig.DBAPP_PASSWD + ";Database=" + DBConfig.DBAPP_DBNAME + ";Connect Timeout=30";
string cmdstr = "SELECT * FROM WIRELESS_POLICE_T";

// Create the adapter with the selectCommand txt and the connection string
SqlDataAdapter adapter = new SqlDataAdapter(cmdstr, constr);

// Create the builder for the adapter to automatically generate the Command when needed
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

// Create and fill the DataSet using the WIRELESS_POLICE_T
DataSet dataset = new DataSet();
adapter.Fill(dataset, "WIRELESS_POLICE_T");

// Get the WIRELESS_POLICE_T table from the dataset
DataTable table = dataset.Tables["WIRELESS_POLICE_T"];

// Indicate DataColumn WLPid is unique, This is required by the SqlCommandBuilder to update the WIRELESS_POLICE_T table
table.Columns["WLPid"].Unique = true;

// New row from the WIRELESS_POLICE_T table
DataRow row = table.NewRow();

// Update a column
//row["xxx"] = xxx;
// 你的赋值语句
// Now update the WIRELESS_POLICE_T using the adapter
// The OracleCommandBuilder will create the UpdateCommand for the adapter to update the WIRELESS_POLICE_T table
adapter.Update(dataset, "WIRELESS_POLICE_T");

方法三：构造SQL语句（类似java中的PreparedStatement）

            string constr = "Server=" + DBConfig.DBAPP_IP + ";user id=" + DBConfig.DBAPP_USER + ";password=" + DBConfig.DBAPP_PASSWD + ";Database=" + DBConfig.DBAPP_DBNAME + ";Connect Timeout=30";
            SqlConnection conn = new SqlConnection(constr);

            // 此处可能存在sql语句中含有单引号的问题
            /**
            string cmdstr = "update WIRELESS_PERSON_T set PersonName='"+person.getPersonName()
                +"', PersonSex='"+person.getPersonSex()+"', YID='"+person.getYID()
                +"', caseinfoid='"+person.getCaseinfoid()+"', Kind='"+person.getKind()
                +"', caseremark='"+person.getCaseremark()+"', ArrivalKind='"+person.getArrivalKind()
                +"' where personId="+person.getPersonId();
             * */

            string cmdstr = "update WIRELESS_PERSON_T set PersonName=@PersonName, PersonSex='" + person.getPersonSex() 
                + "', YID=@YID, caseinfoid='" + person.getCaseinfoid() + "', Kind='" + person.getKind()
                + "', caseremark=@Caseremark, ArrivalKind='" + person.getArrivalKind() + "' where PersonId=" + person.getPersonId();
            Console.WriteLine(cmdstr);
            //SqlCommand command = new SqlCommand(cmdstr, conn);
            SqlCommand command = conn.CreateCommand();
            command.CommandText = cmdstr;
            command.Parameters.Add(new SqlParameter("PersonName", person.getPersonName()));
            command.Parameters.Add(new SqlParameter("YID", person.getYID()));
            command.Parameters.Add(new SqlParameter("Caseremark", person.getCaseremark()));

            try
            {
                conn.Open();
                command.ExecuteNonQuery();
                Console.WriteLine("保存信息成功！");
            }
            catch (Exception e2)
            {
                MessageBox.Show("保存出错！" + e2.Message);
                return;

            }
            finally
            {
                conn.Close();
            }

上述代码中person为一个对象实例。