INSERT语句中的列少于VALUES子句中指定的值 [英] There are fewer columns in the INSERT statement than values specified in the VALUES clause

问题描述

我使用了这个sql代码，但它说有更少的列声明但不是真的!!

 SQL = < span class =code-string> 插入Lezioni（NomeC，NomeS，Giorno，OraIni，OraFin，Costo）价值观（' + NomeCorso +  '，' + NomeSala +  '，' + Giorno +  '， ' + OraIniz +  '，' + OraFin +  '， + Costo +  ） 

我尝试过：



我试图查看代码，但它是对的我真的不知道该怎么办!!

解决方案

大多数LIK你在Costo中有一个十进制值，你的小数分隔符是逗号。例如，如果成本是1,23那么你的陈述看起来像

  INSERT   INTO  Lezioni（...） VALUES （...， 1 ， 23 ）

导致值列表中的额外列



在SQL中，逗号分隔符必须是点，而不是1,23，值应该是1.23。



已经指出使用参数将解决许多其他问题，因此这将是首选的解决方案。请参阅 SqlParameter类（System.Data.SqlClient） [ ^ ]



但是，如果您选择不使用参数，则需要确保使用逗号将十进制值转换为字符串。考虑以下示例

  decimal  costo =  1  .23M; 
  string  test = costo.ToString（System.Globalization.CultureInfo.GetCultureInfo（  en-US））; 

谷歌这个并开始阅读：SQL注入攻击。这将说明为什么你所做的是一个主要的安全风险。



然后Google用于C＃SQL参数化查询以了解如何处理它它甚至可以解决您的问题！

 SQL =   INSERT INTO Lezioni（NomeC，NomeS，Giorno，OraIni，OraFin，Costo）VALUES（' + NomeCorso +  < span class =code-string>'，' + NomeSala +  '，' + Giorno +  '，' + OraIniz +  '，' + OraFin +  '， + Costo +  ） 

一个人无法锻炼任何东西，这是什么sting是变量 SQL 的包含，因为其中一个变量可能有一个不幸的包含像

 NomeCorso =   A'，'B; 

这是一个SQL注入。



永远不要通过连接字符串来构建SQL查询。迟早，您将使用用户输入来执行此操作，这会打开一个名为SQL注入的漏洞，这对您的数据库很容易并且容易出错。

名称中的单引号你的程序崩溃。如果用户输入像Brian O'Conner这样的名称可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞，崩溃是最少的问题，恶意用户输入，并且它被提升为具有所有凭据的SQL命令。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

按示例进行SQL注入攻击 [ ^ ]

PHP：SQL注入 - 手册 [ ^ ]

SQL注入预防备忘单 - OWASP [ ^ ]

I used this sql code but it said that there are fewer columns statement but is not true!!

SQL = "INSERT INTO Lezioni(NomeC, NomeS, Giorno, OraIni, OraFin, Costo)VALUES('" + NomeCorso + "', '" + NomeSala + "', '" + Giorno + "', '" + OraIniz + "', '" + OraFin + "', " + Costo + ")"

What I have tried:

I tried to review the code but it's right i really don't know what to do!!

解决方案

Most likely you have a decimal value in Costo and the decimal separator for you is comma. For example if the cost is 1,23 then your statement would look like

INSERT INTO Lezioni ( ... ) VALUES (..., 1,23)

That causes the 'extra' column in the value list

In SQL the comma separator needs to be point so instead of 1,23 the value should be 1.23.

As already pointed out using parameters would fix that among many other problems so that would be the preferred solution. See SqlParameter Class (System.Data.SqlClient)[^]

However, if you choose not to use parameters then you need to ensure that the decimal value is converted to string using comma. Consider the following example

decimal costo = 1.23M;
string test =  costo.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US"));

Google this and start reading: "SQL injection attack". It's going to spell out why what you're doing is a major security risk.

Then Google for "C# SQL Parameterized queries" to find out what to do about it with the added bonus that it even fixes your problem!

SQL = "INSERT INTO Lezioni(NomeC, NomeS, Giorno, OraIni, OraFin, Costo)VALUES('" + NomeCorso + "', '" + NomeSala + "', '" + Giorno + "', '" + OraIniz + "', '" + OraFin + "', " + Costo + ")"

One can't workout anything from this, what is interesting is the contain of the variable SQL because 1 of the variables can have an unfortunate contain like

NomeCorso= "A','B";

That is an SQL injection.

Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]

这篇关于INSERT语句中的列少于VALUES子句中指定的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！