在数据库中存储Ascii代码 [英] Storing Ascii Code in Database

问题描述

你好

我有一个包含Ascii Code的字符串变量。

我试图将它保存在数据库中的（nvachar），（Text）字段中，但是数据库

会为它添加几个（？）字符。为什么会这样？

如何正确存储该变量？



这是代码

< pre lang =cs> 使用（SqlConnection connection = new SqlConnection（connectionstring））
{
尝试
{
connection.Open（）;
string insrt_statment = ;
string insrt_statment2 = ;
string str_sign = Ã\？ < ????4vÛe????ÑÊU§?? U£M？¡s？uV＃üüÛ_ ??< v; >

int id;
SqlCommand命令;



insrt_statment = INSERT INTO [Folders] VALUES（ ' + folderPath + '，' + parameters + '）; SELECT SCOPE_IDENTITY（）;;
command = new SqlCommand（insrt_statment，connection）;
command.CommandType = CommandType.Text;
id = Convert.ToInt32（command.ExecuteScalar（））;
insrt_statment2 = INSERT INTO [Files] VALUES（' + fileNames [ 0 ]。FullName + '，' + id + '，' + fileNames [ 0 ].LastAccessTime + '，' + fileNames [ 0 ]。LastWriteTime + '，' + str_sign + '，GETDATE（））;;

command = new SqlCommand（insrt_statment2，connection）;
command.CommandType = CommandType.Text;
int aux = command.ExecuteNonQuery（）;


}
catch （异常错误）
{
label2.Text = < span class =code-string> 连接错误 + err.Message;
}
最后
{
if （连接.State == ConnectionState.Open）
{
connection.Close（）;
}
}
}

解决方案

首先要做的就是停止这样做！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



当你连接字符串时，你构建一个新的字符串，直接发送给SQL - 所以当你发送你的特殊字符列表时它会导致两个问题：

1）让你彻底摧毁你的数据库。

2）这意味着SQL必须尝试将你的数据解释为正常 文本字符串 - 它不是，它是一个Unicode字符串。



所以，使用参数化查询，你的问题都会消失！

 insrt_statment2 =  插入[Files] VALUES（@FILENAME，@ LASTATIME @LASTWTIME，@ DATA，GETDATE（））;; 
 command =  new  SqlCommand（insrt_statment2，connection）; 
 command.Parameters.AddWithValue（  @ FILENAME，fileNames [ 0 ]全名）。 
 command.Parameters.AddWithValue（  @ LASTATIME，fileNames [ 0 ]的LastAccessTime）。 
 command.Parameters.AddWithValue（  @ LASTWTIME，fileNames [ 0 ] LastWriteTime）。 
 command.Parameters.AddWithValue（  @ DATA，str_sign）; 

作为奖励，它使您的代码更具可读性......



BTW：插入值时，它是一个非常好的想法，在INSERT查询中命名要插入的所有列...

Hello
I have a string variable contains Ascii Code in it .
I tried to save it in (nvachar),(Text) fields in the database but the database
adds several (?) characters to it . Why does that happens?
And how to store that variable correctly?

Here is the code

using (SqlConnection connection = new SqlConnection(connectionstring))
                {
                    try
                    {
                        connection.Open();
                        string insrt_statment = "";
                        string insrt_statment2 = "";
                         string str_sign = "Ã\<š4vÛe­ˆÑÊU§U£MÌ¡suV#ÌüÛ_‘<v";>

                        int id;
                        SqlCommand command;



                        insrt_statment = "INSERT INTO [Folders] VALUES('" + folderPath + "','" + parameters + "');SELECT SCOPE_IDENTITY();";
                        command = new SqlCommand(insrt_statment, connection);
                        command.CommandType = CommandType.Text;
                        id = Convert.ToInt32( command.ExecuteScalar());
                        insrt_statment2 = "INSERT INTO [Files] VALUES('" + fileNames[0].FullName + "','" + id + "', '" + fileNames[0].LastAccessTime + "','" + fileNames[0].LastWriteTime + "','" + str_sign + "',GETDATE());";

                        command = new SqlCommand(insrt_statment2, connection);
                        command.CommandType = CommandType.Text;
                        int aux = command.ExecuteNonQuery();


                    }
                    catch (Exception err)
                    {
                        label2.Text = "error in connection" + err.Message;
                    }
                    finally
                    {
                        if (connection.State == ConnectionState.Open)
                        {
                            connection.Close();
                        }
                    }
                }

解决方案

The first thing to do is stop doing that! Do not 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. Use Parametrized queries instead.

When you concatenate strings, you build a new string that is sent as is direct to SQL - so when you send your special characters list like that it causes two problems:
1) It leaves you wide open to be destroying your DB.
2) it means SQL has to try to interpret your data as a "normal" text string - which it isn't, it's a Unicode string.

So, use a parameterised query, and both your problems will disappear!

insrt_statment2 = "INSERT INTO [Files] VALUES(@FILENAME, @LASTATIME @LASTWTIME, @DATA, GETDATE());";
command = new SqlCommand(insrt_statment2, connection);
command.Parameters.AddWithValue("@FILENAME", fileNames[0].FullName);
command.Parameters.AddWithValue("@LASTATIME", fileNames[0].LastAccessTime);
command.Parameters.AddWithValue("@LASTWTIME", fileNames[0].LastWriteTime);
command.Parameters.AddWithValue("@DATA", str_sign); 

And as a bonus, it makes your code a lot more readable, too...

BTW: When inserting values, it is a very good idea to name all the columns you want to insert in teh INSERT query...

这篇关于在数据库中存储Ascii代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！