. NET writes non-text types to SQL

In general, when updating a DataTable or DataSet, if you do not use SqlParameter, when the input SQL statement is ambiguous, such as the string contains single quotation marks, the program will have an error, and others can easily splicing SQL statements to inject attacks.

?

123456789101112131415161718

stringsql = "update Table1 set name = ‘Pudding‘ where ID = ‘1‘";//未采用SqlParameterSqlConnection conn = newSqlConnection();conn.ConnectionString = "Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true";//连接字符串与数据库有关SqlCommand cmd = newSqlCommand(sql, conn);try{    conn.Open();    return(cmd.ExecuteNonQuery());}catch(Exception){    return-1;    throw;}finally{    conn.Close();}

The above code does not adopt SqlParameter, in addition to the existence of security issues, the method is not able to resolve the binary stream update, slice file. The above problems can be solved by using SqlParameter, and there are two common ways to use them, the Add method and the AddRange method.

First, the Add method

?

1234	SqlParameter sp = newSqlParameter("@name","Pudding");cmd.Parameters.Add(sp);sp = newSqlParameter("@ID","1");cmd.Parameters.Add(sp);

This method can only add one SqlParameter at a time. The function of the above code is to update the field name with the ID value equal to 1 to pudding (person name).

Second, AddRange method

?

12	SqlParameter[] paras = newSqlParameter[] { new SqlParameter("@name","Pudding"),newSqlParameter("@ID","1") };cmd.Parameters.AddRange(paras);

Obviously, the Add method is inconvenient when adding multiple SqlParameter, at which point the AddRange method can be used. Here is the code to store and read the picture through SqlParameter to the database.

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758

publicintSavePhoto(stringphotourl){    FileStream fs = newFileStream(photourl, FileMode.Open, FileAccess.Read);//创建FileStream对象，用于向BinaryReader写入字节数据流    BinaryReader br = newBinaryReader(fs);//创建BinaryReader对象，用于写入下面的byte数组    byte[] photo = br.ReadBytes((int)fs.Length);//新建byte数组，写入br中的数据    br.Close();//记得要关闭br    fs.Close();//还有fs    stringsql = "update Table1 set photo = @photo where ID = ‘0‘";    SqlConnection conn = newSqlConnection();    conn.ConnectionString = "Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true";    SqlCommand cmd = newSqlCommand(sql, conn);    SqlParameter sp = newSqlParameter("@photo", photo);    cmd.Parameters.Add(sp);    try    {        conn.Open();        return(cmd.ExecuteNonQuery());    }    catch(Exception)    {        return-1;        throw;    }    finally    {        conn.Close();    }}publicvoidReadPhoto(stringurl)    {        stringsql = "select photo from Table1 where ID = ‘0‘";        SqlConnection conn = newSqlConnection();        conn.ConnectionString = "Data Source=.\\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\\Database.mdf;User Instance=true";        SqlCommand cmd = new SqlCommand(sql, conn);        try        {            conn.Open();            SqlDataReader reader = cmd.ExecuteReader();//采用SqlDataReader的方法来读取数据            if (reader.Read())            {                byte[] photo = reader[0] asbyte[];//将第0列的数据写入byte数组                FileStream fs = newFileStream(url,FileMode.CreateNew);创建FileStream对象，用于写入字节数据流                fs.Write(photo,0,photo.Length);//将byte数组中的数据写入fs                fs.Close();//关闭fs            }            reader.Close();//关闭reader        }        catch(Exception ex)        {            throw;        }        finally        {            conn.Close();        }    }}

. NET writes non-text types to SQL