没有数据时插入null

本文介绍了没有数据时插入null的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

当我向sql server输入数据时，通常会填写一些内容，而有些则不会，当有空数据时，我想设置为"NULL".

when I input data to sql server there is something that is usually filled in and there are those that don''t, when there is empty data I want to set ''NULL''.

string query = "UPDATE MASTER_DOSEN SET NIP = '" + NIP + "', NIDN = '" + NIDN + "', GELAR_DEPAN = '" + GELAR_DEPAN + "', NAMA = '" + NAMA + "', GELAR_BELAKANG = '" + GELAR_BELAKANG + "', HOMEBASE = '" + HOMEBASE + "', KDPST = '" + KDPST + "', TMP_LAHIR = '" + TMP_LAHIR + "', TGL_LAHIR = '" + TGL_LAHIR + "', DOMISILI = '" + DOMISILI + "', ALAMAT = '" + ALAMAT + "', HANDPHONE = '" + HANDPHONE + "', EMAIL = '" + EMAIL + "' WHERE ID = '" + lblID_Dosen.Text.Trim() + "'";

我尝试过的事情: 我已经试过了

What I have tried: I have tried it

if (NIP == "") { NIP = "NULL"; } if (NIDN == "") { NIDN = "NULL"; } if (GELAR_DEPAN == "") { GELAR_DEPAN = "NULL"; } if (KDPST == "") { KDPST = "NULL"; } if (TMP_LAHIR == "") { TMP_LAHIR = "NULL"; } if (DOMISILI == "") { DOMISILI = "NULL"; } clsSQLServer db = new clsSQLServer("SIDANG"); string query = "UPDATE MASTER_DOSEN SET NIP = '" + NIP + "', NIDN = '" + NIDN + "', GELAR_DEPAN = '" + GELAR_DEPAN + "', NAMA = '" + NAMA + "', GELAR_BELAKANG = '" + GELAR_BELAKANG + "', HOMEBASE = '" + HOMEBASE + "', KDPST = '" + KDPST + "', TMP_LAHIR = '" + TMP_LAHIR + "', TGL_LAHIR = '" + TGL_LAHIR + "', DOMISILI = '" + DOMISILI + "', ALAMAT = '" + ALAMAT + "', HANDPHONE = '" + HANDPHONE + "', EMAIL = '" + EMAIL + "' WHERE ID = '" + lblID_Dosen.Text.Trim() + "'"; int rec = db.SQLCommand(query);

当我在这样的数据库中看到 NIP =``NULL'' NIDN =``NULL'' ETC

when i see in database like this NIP = ''NULL'' NIDN = ''NULL'' ETC

推荐答案

不要那样做！切勿串联字符串以构建SQL命令.它使您对意外或蓄意的SQL注入攻击敞开大门，这可能会破坏整个数据库.始终改为使用参数化查询. 连接字符串时，会导致问题，因为SQL会收到以下命令: Don''t do it like that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Always use Parameterized queries instead. When you concatenate strings, you cause problems because SQL receives commands like: SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

用户添加的引号就SQL而言终止了字符串，您会遇到问题.但是情况可能更糟.如果我随便输入以下内容:"x"; DROP TABLE MyTable;-然后SQL会收到一个非常不同的命令:

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x'';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

哪个SQL可以将其视为三个单独的命令:

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

一个完全有效的SELECT

A perfectly valid SELECT

DROP TABLE MyTable;

一个完全有效的删除表"命令

A perfectly valid "delete the table" command

--'

，其他所有内容都是注释. 这样做是:选择任何匹配的行，从数据库中删除该表，然后忽略其他任何内容. 所以总是使用参数化查询！或者准备经常从备份中还原数据库.您会定期备份吗? 额外的好处是，它也可以解决您的问题...

And everything else is a comment. So it does: selects any matching rows, deletes the table from the DB, and ignores anything else. So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don''t you? As an extra bonus, it''ll fix your problem as well...

TMP_LAHIR = "NULL"

"NULL"和NULL之间存在很大的差异. 第一个是字符的字符串，就像"OOPS"一样.第二个是例如SQL Server中的定义值. 实现查询语句的方式是，将值包装在单引号中，无论它是实数还是NULL，因此已向服务器发送了一个字符串，而不是NULL 您需要对其进行处理，以便如果它不是NULL，则在单引号中有一个值"pre-wrapped"，如果是，则不带单引号.我实际上使用一个函数来做到这一点(PHP).看起来像是通用代码格式.

There''s a world of difference between "NULL" and NULL. The first one is a sting of characters, just like "OOPS". The second one is a defined value in, for example, SQL Server. The way you implement the query statement, you wrap the value in single quotes whether it''s real or NULL, so you have sent the server a string instead of NULL You need to process it so that you have a value "pre-wrapped" in single quotes if it''s not NULL and without the single quotes if it is. I actually use a function to do this (php). It would look something like in a generic code format.

val = (val=='''')?"NULL":"''val''";

现在，当插入查询时，仅附加val而不用单引号引起来.

Now, when plugged in to your query, only append the val and do not wrap in single quotes.

string query = "UPDATE MASTER_DOSEN SET NIP = '" + NIP + "', NIDN = '" + NIDN + "', GELAR_DEPAN = '" + GELAR_DEPAN + "', NAMA = '" + NAMA + "', GELAR_BELAKANG = '" + GELAR_BELAKANG + "', HOMEBASE = '" + HOMEBASE + "', KDPST = '" + KDPST + "', TMP_LAHIR = '" + TMP_LAHIR + "', TGL_LAHIR = '" + TGL_LAHIR + "', DOMISILI = '" + DOMISILI + "', ALAMAT = '" + ALAMAT + "', HANDPHONE = '" + HANDPHONE + "', EMAIL = '" + EMAIL + "' WHERE ID = '" + lblID_Dosen.Text.Trim() + "'";

不是您的问题的解决方案，而是您遇到的另一个问题. 切勿通过串联字符串来构建SQL查询.迟早，您将使用用户输入来执行此操作，这将打开一个名为"SQL注入"的漏洞的大门，这对您的数据库很危险，并且容易出错. 名称中的单引号会导致程序崩溃.如果用户输入的名称如"Brian O" Conner"可能会使您的应用程序崩溃，则这是一个SQL注入漏洞，而崩溃是最少的问题，这是恶意的用户输入，并且使用所有凭据将其提升为SQL命令. SQL注入-Wikipedia [ ^ ] SQL注入 [ ^ ] SQL注入攻击示例 [ ^ ] PHP:SQL注入-手册 [ ^ ] SQL注入预防作弊表-OWASP [ ^ ] 如何在没有技术术语的情况下解释SQL注入? -信息安全堆栈交换 [ ^ ]

Not a solution to your question, but another problem you have. 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[^] How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]