title: Is it possible to use sql parameterized query to insert into encrypted columns with symmetric encryption
body: <p>I am working on <strong>database encryption</strong>, i.e. <strong>cell based symmetric encryption</strong> in SQL server 2008 Express.
  But the problem is that the parameterized queries for insertion are not working. Help me please . . .</p>
  
  <p>Edit:</p>
  
  <p>I am using the following query as Example:</p>
  
  <pre><code>foreach (var list in from DataRow row in dataTable.Rows select new ArrayList
                             {
                                   String.Format("@var1, {0}", row["Column1"]), 
                                   String.Format("@var2, {0}", row["Column2"]),
                                   String.Format("@var3, {0}", row["Column3"])
                             })
      {
           var query = String.Format(@"OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE {1} 
                       INSERT INTO TableA (Column1, Column2, Column3) VALUES (@ENCRYPTBYKEY(KEY_GUID('symKey'), '{2}'), ENCRYPTBYKEY(KEY_GUID('symKey'), '{3}'), ENCRYPTBYKEY(KEY_GUID('symKey'), '{4}'))", symKey, symCer, "@var1", "@var2", "@var3");
           con.Execute.ExecuteParameterizedQuery(query, list);
      }
  
  public string ExecuteParameterizedQuery(string query, ArrayList parametersList)
  {
        errorFlag = Connect(un, pasw, 3);
        if ((String.CompareOrdinal(errorFlag, "Open") == 0))
        {
            var myTran = myConnection.BeginTransaction();
            cmd = new SqlCommand(query, myConnection) { Transaction = myTran };
            for (var i = 0; i &lt; parametersList.Count; i++)
            {
                var split = parametersList[i].ToString().Split(',');
                cmd.Parameters.AddWithValue(split[0], split[1]);
            }
            try
            {
                cmd.CommandText = query;
                cmd.ExecuteNonQuery();
                myTran.Commit();
                errorFlag = string.Empty;
            }
            catch (Exception e)
            {
                errorFlag = e.Message;
            }
            finally
            {
                myConnection.Close();
                myConnection.Dispose();
            }
            return errorFlag;
        }
        myConnection.Close();
        myConnection.Dispose();
        return errorFlag;
    }
  </code></pre>
  
  <p>EDIT 2:</p>
  
  <pre><code>CREATE PROCEDURE ng_encryptString
  (
      @PlaneText VARCHAR(500), @SipherText VARBINARY(2000) OUT
  )
  AS
  BEGIN
  OPEN SYMMETRIC KEY symKey DECRYPTION BY CERTIFICATE SymCert 
  SELECT ENCRYPTBYKEY(KEY_GUID('SymKey'), @PlaneText)
  END
  </code></pre>