How to Safely Insert Data into an Access Database Using Parameters?

Use parameters to insert data into Access database

Inserting Access database data directly using hard-coded parameters can cause problems, especially when working with text that contains single quotes. To solve this problem, you can use parameters in the query instead of direct string values.

Step 1: Replace hardcoded parameters with placeholders

In the provided code, replace each hardcoded parameter in the INSERT query with a placeholder prefixed with "@". For example:

<code>cmd.CommandText = "INSERT INTO bookRated([title], [rating],  [review], [frnISBN], [frnUserName])VALUES('@title', '@rating','@review','@ISBN', '@userName')";</code>

Step 2: Add OleDbParameters to DbCommand

Create an OleDbParameter instance and add it to the DbCommand.Parameters property. Parameter names should match the placeholder names used in the query:

<code>cmd.Parameters.AddRange(new OleDbParameter[]
{
    new OleDbParameter("@title", title),
    new OleDbParameter("@rating", rating),
    ...
});</code>

Modified code:

<code>[WebMethod]
public void bookRatedAdd(string title, int rating, string review, string ISBN, string userName)
{
   using (OleDbConnection conn = new OleDbConnection(
         "Provider=Microsoft.Jet.OleDb.4.0;"+
         "Data Source="+Server.MapPath("App_Data\BookRateInitial.mdb"));
   {

      conn.Open();

      // DbCommand 也实现了 IDisposable 接口
      using (OleDbCommand cmd = conn.CreateCommand())
      {
           // 使用占位符创建命令
           cmd.CommandText = 
              "INSERT INTO bookRated "+
              "([title], [rating],  [review], [frnISBN], [frnUserName]) "+
              "VALUES(@title, @rating, @review, @isbn, @username)";

           // 添加命名参数
           cmd.Parameters.AddRange(new OleDbParameter[]
           {
               new OleDbParameter("@title", title),
               new OleDbParameter("@rating", rating),
               // ... 其他参数
           });

           // 执行
           cmd.ExecuteNonQuery();
      }
   }
}</code>

By using parameters, the database can correctly handle special characters such as single quotes, thus ensuring data integrity.

The above is the detailed content of How to Safely Insert Data into an Access Database Using Parameters?. For more information, please follow other related articles on the PHP Chinese website!