操作数据库（防注入攻击）

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Text;
  5 using System.Threading.Tasks;
  6 using System.Data.SqlClient;
  7 
  8 namespace 作业操作数据库
  9 {
 10     class Program
 11     {
 12         static void Main(string[] args)
 13         {
 14             //作业：
 15             //1、让用户输入一个代号，查到一条数据（car,info），并显示
 16             //2、输出提示，输入1删除该数据，输入2修改数据，输入3添加数据
 17             //3、当用户输入1，把查到的这条数据删除
 18             //4、当用户输入2，提示输入其他值，主键不修改，输入完成，修改这项数据
 19             //5、当用户输入3，提示用户输入所有的列，然后保存到数据库
 20 
 21             string [] AR = new string[] { "code","姓名","性别","民族","生日"}; 
 22             //用户输入代号
 23             Console.WriteLine("请输入要查询的代号");
 24             string code = Console.ReadLine();
 25             
 26             //造连接对象
 27             SqlConnection conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=diushiDEwutong0");
 28 
 29             //造命令对象
 30             SqlCommand cmd = conn.CreateCommand();
 31 
 32             //给命令对象SQL语句
 33             cmd.CommandText = "select * from Info where code=@code";
 34             //防注入
 35             //该变量绑定参数
 36             cmd.Parameters.Clear();
 37             cmd.Parameters.AddWithValue("@code",code);
 38             
 39             //打开连接
 40             conn.Open();
 41 
 42             SqlDataReader dr = cmd.ExecuteReader();
 43 
 44             if (dr.HasRows)
 45             {
 46                 while (dr.Read())
 47                 {
 48                     int i = 0;
 49 
 50                     while (i < dr.FieldCount)
 51                     {
 52                         Console.Write(AR[i] + "\t");
 53                         i++;
 54                     }
 55                     Console.Write("\n");
 56                     i = 0;
 57                     while (i < dr.FieldCount)
 58                     {
 59                         Console.Write(dr[i] + "\t");
 60                         i++;
 61                     }
 62                     Console.Write("\n");
 63                 }
 64             }
 65             else
 66             {
 67                 Console.WriteLine("未查询到数据");
 68                 while (true)
 69                 {
 70                     dr.Close();
 71                     Console.WriteLine("请重新输入要查询的代号");
 72                     code = Console.ReadLine();
 73 
 74                     cmd.CommandText = "select * from Info where code=@code";
 75                     cmd.Parameters.Clear();
 76                     cmd.Parameters.AddWithValue("@code", code);
 77                     dr = cmd.ExecuteReader();
 78                     if (dr.HasRows)
 79                     {
 80                         while (dr.Read())
 81                         {
 82                             int i = 0;
 83 
 84                             while (i < dr.FieldCount)
 85                             {
 86                                 Console.Write(AR[i] + "\t");
 87                                 i++;
 88                             }
 89                             Console.Write("\n");
 90                             i = 0;
 91                             while (i < dr.FieldCount)
 92                             {
 93                                 Console.Write(dr[i] + "\t");
 94                                 i++;
 95                             }
 96                             Console.Write("\n");
 97                         }
 98                         break;
 99                     }
100                     else
101                     {
102                         Console.WriteLine("未查询到数据");
103                     }
104                 }
105             }
106 
107 
108             while (true)
109             {
110                 Console.WriteLine("输入1删除该数据，输入2修改数据，输入3添加数据，输入其他跳出程序");
111                 string  s = Console.ReadLine();
112                 if (s ==" 1")
113                 {
114                     if (dr.HasRows)
115                     {
116                         Console.WriteLine("确认删除请输入：1");
117                         int sc = int.Parse(Console.ReadLine());
118                         if (sc == 1)
119                         {
120                             dr.Close();
121                             cmd.CommandText = "delete  from Info where code=@code";
122                             cmd.Parameters.Clear();
123                             cmd.Parameters.AddWithValue("@code", code);
124                             cmd.ExecuteNonQuery();
125                             Console.WriteLine("删除成功");
126                         }
127                         else
128                         {
129                             Console.WriteLine("取消操作");
130                         }
131                     }
132                 }
133                 else if (s == "2")//修改内容
134                 {
135                     while (true)
136                     {
137                         dr.Close();
138                         Console.Write("请输入要修改的列名：");
139                         string str = Console.ReadLine();
140                         string gai = "";
141                         switch (str)
142                         {
143                             case "姓名":
144                                 gai = "Name";
145                                 break;
146                             case "性别":
147                                 gai = "Sex";
148                                 break;
149                             case "民族":
150                                 gai = "Nation";
151                                 break;
152                             case "生日":
153                                 gai = "Birthday";
154                                 break;
155                             default: 
156                                 Console.WriteLine("输入错误");
157                                 continue;
158                                 
159                         }
160                         if (gai == "Sex")
161                         {
162                             Console.Write("请输入要修改的内容：");
163                             bool sex = Console.ReadLine() == "男" ? true : false;
164                             cmd.CommandText = "update Info set " + gai + "='" + sex + "' where Code=@code";
165                             cmd.ExecuteNonQuery();
166                         }
167                         else if (gai == "Nation")
168                         {
169                             string nationcode = "n001";
170                             Console.Write("请输入要修改的内容：");
171                             string nation = Console.ReadLine();
172                             cmd.CommandText = "select Code from Nation where Name = '" + nation + "'";
173                             dr.Close();
174                             dr = cmd.ExecuteReader();
175                             if (dr.HasRows)
176                             {
177                                 dr.Read();
178                                 nationcode = dr[0].ToString();
179                             }
180                             cmd.CommandText = "update Info set " + gai + "='" + nationcode + "' where Code=@code";
181                             dr.Close();
182                             cmd.ExecuteNonQuery();
183                             }
184                         else
185                         {
186                             Console.Write("请输入要修改的内容：");
187                             string neirong = Console.ReadLine();
188                             cmd.CommandText = "update Info set " + gai + "='" + neirong + "' where Code=@code";
189                             cmd.ExecuteNonQuery();
190                         }
191                         Console.WriteLine("修改成功!");
192                         Console.WriteLine("修改后的内容为：");
193 
194                         cmd.CommandText = "select * from Info where code=@code";
195                         cmd.Parameters.Clear();
196                         cmd.Parameters.AddWithValue("@code", code);
197                         dr = cmd.ExecuteReader();
198                         if (dr.HasRows)
199                         {
200                             while (dr.Read())
201                             {
202                                 int i = 0;
203 
204                                 while (i < dr.FieldCount)
205                                 {
206                                     Console.Write(AR[i] + "\t");
207                                     i++;
208                                 }
209                                 Console.Write("\n");
210                                 i = 0;
211                                 while (i < dr.FieldCount)
212                                 {
213                                     Console.Write(dr[i] + "\t");
214                                     i++;
215                                 }
216                                 Console.Write("\n");
217                             }
218                         }
219                         Console.WriteLine("是否继续修改？继续修改输入1；不修改输入任意键");
220                         int b = int.Parse(Console.ReadLine());
221                         if (b == 1)
222                         {
223                             Console.WriteLine("继续修改");
224                         }
225                         else
226                         {
227                             break;
228                         }
229 
230                     }
231                     
232                 }
233                 else if (s == "3")//添加数据
234                 {
235                     //让用户输入要添加的内容
236                     Console.WriteLine("请输入要添加的代号：");
237                      code = Console.ReadLine();
238 
239                     Console.WriteLine("请输入姓名：");
240                     string name = Console.ReadLine();
241 
242                     Console.WriteLine("请输入性别：（男或女）");
243                     bool sex = Console.ReadLine() == "男" ? true : false;
244 
245                     Console.WriteLine("请输入民族：");
246                     string nation = Console.ReadLine();
247 
248                     Console.WriteLine("请输入生日：");
249                     string birthday = Console.ReadLine();
250 
251                     string nationcode = "n001";
252 
253                     //将民族名称转为名族代号
254                     cmd.CommandText = "select Code from Nation where Name = '" + nation + "'";
255                     dr.Close();
256                     
257                     dr = cmd.ExecuteReader();
258                     if (dr.HasRows)
259                     {
260                         dr.Read();
261                         nationcode = dr[0].ToString();
262                     }
263                     //往Info表添加数据
264                     cmd.CommandText = "insert into Info values('" + code + "','" + name + "','" + sex + "','" + nationcode + "','" + birthday + "')";
265                     dr.Close();
266                     cmd.ExecuteNonQuery();
267                     Console.WriteLine("添加成功！");
268 
269                     Console.WriteLine("添加的内容为：");
270                     cmd.CommandText = "select * from Info where code=@code";
271                     cmd.Parameters.Clear();
272                     cmd.Parameters.AddWithValue("@code", code);
273                     dr = cmd.ExecuteReader();
274                     if (dr.HasRows)
275                     {
276                         while (dr.Read())
277                         {
278                             int i = 0;
279 
280                             while (i < dr.FieldCount)
281                             {
282                                 Console.Write(AR[i] + "\t");
283                                 i++;
284                             }
285                             Console.Write("\n");
286                             i = 0;
287                             while (i < dr.FieldCount)
288                             {
289                                 Console.Write(dr[i] + "\t");
290                                 i++;
291                             }
292                             Console.Write("\n");
293                         }
294                     }
295 
296                 }
297                 else
298                 {
299                     Console.WriteLine("程序跳出");
300                     break;
301                 }
302             }
303             conn.Close();
304             Console.ReadLine();
305 
306 
307         }
308     }
309 }

转载于:https://www.cnblogs.com/zl1121102942/p/5795345.html