c# - 插入语句奇怪的错误

Question

我在 Azure 中调用 Web 服务并使用以下方法填充数据库。我不知道怎么了..

using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = String.Format("INSERT INTO UserFiles VALUES('" + obj.userRef.ToString() + "','name','name','name','name','name','name'");
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
      conn.Close();
   }
}

这是错误消息:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'name'.

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)<br/>
at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at _4900ProjectDesktopInterface.Form1.uploadbutton_Click(Object sender, EventArgs e) in C:\Users\Ken\Documents\GitHub\MegaFileUploadConversionService\TestingTool\4900ProjectDesktopInterface\Form1.cs:line 152\r\nClientConnectionId:fb95122f-415b-484d-9438-903f0bf2aad0"

Answer 1

你的cmdText还需要一个)在;

using (SqlConnection conn = new SqlConnection(cs))
{
   using (SqlCommand command = conn.CreateCommand())
   {
      conn.Open();
      string cmdText = String.Format("INSERT INTO UserFiles VALUES(@userRef, @name1, @name2, @name3, @name4, @name5, @name6)");
      command.Parameters.AddVithValue("@userRef", obj.userRef.ToString()); 
      command.Parameters.AddVithValue("@name1", name); 
      command.Parameters.AddVithValue("@name2", name); 
      command.Parameters.AddVithValue("@name3", name); 
      command.Parameters.AddVithValue("@name4", name); 
      command.Parameters.AddVithValue("@name5", name); 
      command.Parameters.AddVithValue("@name6", name); 
      command.CommandText = cmdText;
      command.ExecuteNonQuery();
      conn.Close();
   }
}

正如我在 comment 中所说, 你应该总是使用 parameterized queries 。您的代码对 SQL Injection 开放攻击