API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 17th, 2017
81
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 27.03 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Data;
  3. using System.Data.SqlClient;
  4. using MySql.Data.MySqlClient;
  5. using System.Collections;
  6. using System.Linq;
  7. using System.Text;
  8.  
  9. /*
  10. command =connection.CreateCommand();
  11. command.CommandText = "INSERT INTO log(userid,message,date) VALUES(1,'Hello there',NOW());";
  12. MySqlDataReader reader;
  13. reader = command.ExecuteReader();
  14. Console.WriteLine("Executed Query: " + reader);
  15. */
  16. namespace Lightchat_server
  17. {
  18. public class SQL
  19. {
  20. private MySqlConnection connection = new MySqlConnection();
  21. private MySqlDataAdapter data = new MySqlDataAdapter();
  22. private MySqlCommand command = new MySqlCommand();
  23. MySqlDataReader reader;
  24.  
  25.  
  26. public void connect(Boolean flag)
  27. {
  28. if (flag) { Console.WriteLine("Connecting to MySQL server @ localhost"); }
  29. //Set connection string
  30. connection.ConnectionString =
  31. "server=localhost;"
  32. + "database=lightmunnas;"
  33. + "uid=root;"
  34. + "password=redrun11;";
  35. //Connect
  36. connection.Open();
  37. if (flag) { Console.WriteLine(" >Connected"); }
  38. }
  39.  
  40. public void disconnect()
  41. {
  42. try
  43. {
  44. connection.Dispose();
  45. connection.Close();
  46. }
  47. catch
  48. {
  49.  
  50. }
  51. }
  52.  
  53. public void userLogin(String userName) // on client connection
  54. {
  55. command = connection.CreateCommand();
  56. command.CommandText = "SELECT * FROM users WHERE (username = '" + userName + "');";
  57. reader = command.ExecuteReader();
  58.  
  59. if (reader.Read() != false)//Is the client name already in the database?
  60. {
  61. reader.Close(); //close reader
  62.  
  63. command.CommandText = "UPDATE users SET lastlogin=NOW() WHERE username ='" + userName + "';";
  64. command.ExecuteNonQuery();
  65. }
  66. else //add username to the database
  67. {
  68. reader.Close(); //close reader
  69.  
  70. Console.WriteLine(" >Clients first time connecting, adding to database");
  71. command.CommandText = "INSERT INTO users(username,lastlogin,sent) VALUES('" + userName + "',NOW(),0);";
  72. command.ExecuteNonQuery();
  73. }
  74. }
  75.  
  76. public void createChannel(String name, String password)
  77. {
  78. reader.Close();
  79. command = connection.CreateCommand();
  80. command.CommandText = "INSERT INTO channels(channelname,channelpassword) VALUES('" + name + "','" + password + "');";
  81. command.ExecuteNonQuery();
  82. }
  83.  
  84. public String[] getChannelsByUser(String userName)
  85. {
  86. String id = getIDFromUser(userName);
  87.  
  88. //get number of channels
  89. command.CommandText = "SELECT count(*) FROM channeluser WHERE userid='" + id + "';";
  90. reader = command.ExecuteReader();
  91. reader.Read();
  92. int count = (int)Convert.ToInt32(reader.GetValue(0));
  93. reader.Close();
  94.  
  95. //get channels by ID
  96. command.CommandText = "SELECT channelname FROM channels INNER JOIN channeluser ON channels.id=channeluser.channelid WHERE channeluser.userid ='" + id + "';";
  97. reader = command.ExecuteReader();
  98. String[] channels = new String[count];
  99.  
  100. int loopCount = 0;
  101. while (reader.Read())
  102. {
  103. channels[loopCount] = reader["channelname"].ToString();
  104. loopCount++;
  105. }
  106. reader.Close();
  107. return (channels);
  108. }
  109.  
  110. public int joinChannel(String userName, String channelName, String channelPassword)
  111. {
  112. command = connection.CreateCommand();
  113.  
  114. //Get user id
  115. command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
  116. reader = command.ExecuteReader();
  117. reader.Read();
  118. String id = reader.GetValue(0).ToString();
  119. reader.Close();
  120.  
  121. //Get channel id
  122. command.CommandText = "SELECT id FROM channels WHERE channelname='" + channelName + "';";
  123. reader = command.ExecuteReader();
  124. //check if id exists
  125. String channelid = "-1";
  126. if (reader.Read())
  127. {
  128. channelid = reader.GetValue(0).ToString();
  129. }
  130. reader.Close();
  131.  
  132. if (channelid == "-1") { return (1); }
  133. //Get channel password
  134. command.CommandText = "SELECT channelpassword FROM channels WHERE channelname='" + channelName + "';";
  135. reader = command.ExecuteReader();
  136. reader.Read();
  137. String channelpass = "¤";
  138. if (reader.Read())
  139. {
  140. channelpass = reader.GetValue(0).ToString();
  141. }
  142. reader.Close();
  143.  
  144. //check if user is already in the channel
  145. command.CommandText = "SELECT * FROM channeluser WHERE (channelid='" + channelid + "') AND (userid='" + id + "');";
  146. reader = command.ExecuteReader();
  147. if (!reader.Read())
  148. {
  149. reader.Close();
  150. if (channelpass == channelPassword)
  151. {
  152. //Get user id
  153. command.CommandText = "INSERT INTO channeluser(channelid,userid) VALUES(" + channelid + "," + id + ");";
  154. command.ExecuteNonQuery();
  155. return (0);
  156. }
  157. else
  158. {
  159. return (1);
  160. }
  161. }
  162. reader.Close();
  163. return (2);
  164. }
  165.  
  166. public int leaveChannel(String userName, String channelIndex)
  167. {
  168. int result = 0;
  169. command = connection.CreateCommand();
  170.  
  171. //Get user id
  172. command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
  173. reader = command.ExecuteReader();
  174. reader.Read();
  175. String id = reader.GetValue(0).ToString();
  176. reader.Close();
  177.  
  178. //Get channel id
  179. command.CommandText = "SELECT id FROM channels WHERE channelname='" + channelIndex + "';";
  180. reader = command.ExecuteReader();
  181. reader.Read();
  182. String channelId = reader.GetValue(0).ToString();
  183. reader.Close();
  184.  
  185. try
  186. {
  187. //leave channel
  188. command.CommandText = "DELETE FROM channeluser WHERE (channelid='" + channelId + "') AND (userid='" + id + "');";
  189. command.ExecuteNonQuery();
  190. result = 0;
  191. }
  192. catch
  193. {
  194. result = 1;
  195. }
  196.  
  197. return result;
  198. }
  199.  
  200. public String[] getChannelList()
  201. {
  202. String[] channels;
  203.  
  204. //get number of channels
  205. command.CommandText = "SELECT count(*) FROM channels;";
  206. reader = command.ExecuteReader();
  207. reader.Read();
  208. int count = (int)Convert.ToInt32(reader.GetValue(0));
  209. reader.Close();
  210.  
  211. channels = new String[count];
  212.  
  213. int loopCount = 0;
  214. command.CommandText = "SELECT channelname FROM channels;";
  215. reader = command.ExecuteReader();
  216. while (reader.Read())
  217. {
  218. channels[loopCount] = (string)reader["channelname"];
  219. loopCount++;
  220. }
  221.  
  222. return channels;
  223. }
  224.  
  225. public String getUserFromID(String userid)
  226. {
  227. command = connection.CreateCommand();
  228. command.CommandText = "SELECT username FROM users WHERE id='" + userid + "';";
  229. reader = command.ExecuteReader();
  230. reader.Read();
  231. String name = reader.GetValue(0).ToString(); // get name
  232. reader.Close();
  233. return name;
  234. }
  235.  
  236. public String getIDFromUser(String username)
  237. {
  238. command = connection.CreateCommand();
  239. command.CommandText = "SELECT id FROM users WHERE username='" + username + "';";
  240. reader = command.ExecuteReader();
  241. String id = "";
  242. while (reader.Read())
  243. {
  244. id = reader["id"].ToString(); // get name
  245. }
  246. reader.Close();
  247. Console.WriteLine(id);
  248. return id;
  249. }
  250.  
  251. public void newMessage(String userName, String msg, String channelid)
  252. {
  253. command = connection.CreateCommand();
  254. command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
  255. reader = command.ExecuteReader();
  256. reader.Read();
  257. String id = reader.GetValue(0).ToString(); // get id
  258. reader.Close();
  259.  
  260. command.CommandText = "INSERT INTO log(userid,message,date,channelid) VALUES(" + id + ",'" + msg + "',NOW(),"+ channelid");";
  261. command.ExecuteNonQuery();
  262. command.CommandText = "UPDATE users SET sent = sent + 1 WHERE id=" + id + ";";
  263. command.ExecuteNonQuery();
  264. }
  265.  
  266. public string getLog(String input)
  267. {
  268. try
  269. {
  270. String[] arguments = input.Split('-');
  271.  
  272. String users = null;
  273. bool usersb = false;
  274. String date = null;
  275. bool dateb = false;
  276.  
  277. foreach (string argument in arguments)
  278. {
  279. if (argument.Substring(0, 1) == "u")
  280. {
  281. String[] s;
  282. String noArg = argument.Substring(argument.IndexOf('u') + 1);
  283. if (argument.Contains(','))
  284. {
  285.  
  286. s = noArg.Split(',');
  287. users = "users.username='" + s[0].Trim() + "'";
  288.  
  289. for (int i = 1; i < s.Length; i++)
  290. {
  291. users += " OR users.username='" + s[i].Trim() + "'";
  292. }
  293.  
  294. }
  295. else
  296. {
  297. users = "users.username='" + noArg.Trim() + "'";
  298. }
  299. usersb = true;
  300.  
  301. }
  302. else if (argument.Substring(0, 1) == "d")
  303. {
  304. String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
  305. dateb = true;
  306. if (argument.Contains(','))
  307. {
  308. String[] d;
  309. int argCount = 0;
  310. d = noArg.Split(',');
  311. foreach (String dt in d)
  312. {
  313. if (dt.Contains('*'))
  314. {
  315. String[] dtsplit = dt.Split('*');
  316. foreach (String dts in dtsplit)
  317. {
  318. dts.Trim();
  319. }
  320. if (argCount == 0)
  321. {
  322. date += "log.date BETWEEN CAST('" + dtsplit[0] + "00' AS datetime) AND CAST('" + dtsplit[1] + "00' AS datetime)";
  323. argCount++;
  324. }
  325. else
  326. {
  327. date += "OR log.date BETWEEN CAST('" + dtsplit[0] + "00' AS datetime) AND CAST('" + dtsplit[1] + "00' AS datetime)";
  328. }
  329.  
  330. }
  331. else
  332. {
  333. if (argCount == 0)
  334. {
  335. date += "log.date BETWEEN CAST('" + dt + "00' AS datetime) AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  336. argCount++;
  337. }
  338. else
  339. {
  340. date += " OR log.date BETWEEN CAST('" + dt + "00' AS datetime) AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  341. }
  342. }
  343. }
  344. }
  345. else
  346. {
  347. int argCount = 0;
  348. String d = noArg;
  349. if (d.Contains('*'))
  350. {
  351. String[] dtsplit = d.Split('*');
  352. foreach (String dts in dtsplit)
  353. {
  354. dts.Trim();
  355. }
  356. if (argCount == 0)
  357. {
  358. date += "log.date BETWEEN CAST('" + dtsplit[0] + "00' AS datetime) AND '" + dtsplit[1] + "00'";
  359. argCount++;
  360. }
  361. else
  362. {
  363. date += "OR log.date BETWEEN CAST('" + dtsplit[0] + "00' AS datetime) AND '" + dtsplit[1] + "00'";
  364. }
  365.  
  366. }
  367. else
  368. {
  369. if (argCount == 0)
  370. {
  371. date += "log.date BETWEEN CAST('" + d + "00' AS datetime) AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  372. argCount++;
  373. }
  374. else
  375. {
  376. date += " OR log.date BETWEEN CAST('" + d + "00' AS datetime) AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  377. }
  378. }
  379. }
  380. }
  381.  
  382. }
  383. String args = "SELECT users.username, log.message, log.date, log.channelID FROM log INNER JOIN users ON users.id=log.userid WHERE ";
  384. if (!usersb && dateb)
  385. {
  386. args += date;
  387. }
  388. else if (usersb && !dateb)
  389. {
  390. args += users;
  391. }
  392. else
  393. {
  394. args += users + " AND " + date;
  395. }
  396. args += " ORDER BY log.date ASC;";
  397.  
  398.  
  399.  
  400. String data = null;
  401. Console.WriteLine(args);
  402. Console.WriteLine();
  403.  
  404. command = connection.CreateCommand();
  405. command.CommandText = args;
  406. reader = command.ExecuteReader();
  407.  
  408. while (reader.Read())
  409. {
  410. data += "[" + reader["channelID"].ToString() + "] (" + reader["date"].ToString() + ") <" + reader["username"].ToString() + "> " + reader["message"].ToString() + Environment.NewLine;
  411. }
  412.  
  413. reader.Close();
  414.  
  415. return (data);
  416.  
  417. }
  418. catch (Exception ex)
  419. {
  420. Console.WriteLine(" >/log ERROR: " + ex.Message);
  421. return (null);
  422.  
  423. }
  424. }
  425.  
  426. public string getFileLog(String input)
  427. {
  428. reader.Close();
  429. try
  430. {
  431. String[] arguments = input.Split('-');
  432.  
  433. String senders = null;
  434. bool sendersb = false;
  435. String receivers = null;
  436. bool receiversb = false;
  437. String date = null;
  438. bool dateb = false;
  439. String fileinfo = null;
  440. bool fileinfob = false;
  441.  
  442. foreach (string argument in arguments)
  443. {
  444. if (argument.Substring(0, 1) == "s")
  445. {
  446. String[] s;
  447. String noArg = argument.Substring(argument.IndexOf('s') + 1);
  448. if (argument.Contains(','))
  449. {
  450.  
  451. s = noArg.Split(',');
  452. senders = "users.username='" + s[0].Trim() + "'";
  453.  
  454. for (int i = 1; i < s.Length; i++)
  455. {
  456. senders += " OR users.username='" + s[i].Trim() + "'";
  457. }
  458.  
  459. }
  460. else
  461. {
  462. senders = "users.username='" + noArg.Trim() + "'";
  463. }
  464. sendersb = true;
  465.  
  466. }
  467. else if (argument.Substring(0, 1) == "r")
  468. {
  469. String[] r;
  470. String noArg = argument.Substring(argument.IndexOf('r') + 1);
  471. if (argument.Contains(','))
  472. {
  473.  
  474. r = noArg.Split(',');
  475. receivers = "filetransfers.receiverID='" + getIDFromUser(r[0].Trim()) + "'";
  476.  
  477. for (int i = 1; i < r.Length; i++)
  478. {
  479. receivers += " OR filetransfers.receiverID='" + getIDFromUser(r[i].Trim()) + "'";
  480. }
  481.  
  482. }
  483. else
  484. {
  485. receivers = "filetransfers.receiverID='" + getIDFromUser(noArg.Trim()) + "'";
  486. }
  487. receiversb = true;
  488.  
  489. }
  490. else if (argument.Substring(0, 1) == "f")
  491. {
  492. String[] f;
  493. String noArg = argument.Substring(argument.IndexOf('f') + 1);
  494. if (argument.Contains(','))
  495. {
  496.  
  497. f = noArg.Split(',');
  498. fileinfo = "filetransfers.filename LIKE '%" + f[0].Trim() + "%'";
  499.  
  500. for (int i = 1; i < f.Length; i++)
  501. {
  502. fileinfo += " OR filetransfers.filename LIKE '%" + f[i].Trim() + "%'";
  503. }
  504.  
  505. }
  506. else
  507. {
  508. fileinfo = "filetransfers.filename LIKE '%" + noArg.Trim() + "%'";
  509. }
  510. fileinfob = true;
  511.  
  512. }
  513. else if (argument.Substring(0, 1) == "d")
  514. {
  515. String noArg = argument.Substring(argument.IndexOf('d') + 1).Trim();
  516. dateb = true;
  517. if (argument.Contains(','))
  518. {
  519. String[] d;
  520. int argCount = 0;
  521. d = noArg.Split(',');
  522. foreach (String dt in d)
  523. {
  524. if (dt.Contains('*'))
  525. {
  526. String[] dtsplit = dt.Split('*');
  527. foreach (String dts in dtsplit)
  528. {
  529. dts.Trim();
  530. }
  531. if (argCount == 0)
  532. {
  533. date += "filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
  534. argCount++;
  535. }
  536. else
  537. {
  538. date += "OR filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
  539. }
  540.  
  541. }
  542. else
  543. {
  544. if (argCount == 0)
  545. {
  546. date += "filetransfers.time BETWEEN CAST '" + dt + "00' DATETIME AND DATE_ADD(CAST('" + dt + "00' AS datetime), INTERVAL +5 MINUTE)";
  547. argCount++;
  548. }
  549. else
  550. {
  551. date += " OR filetransfers.time BETWEEN '" + dt + "00' AND DATE_ADD(CAST('" + dt + "00' AS datetime), INTERVAL +5 MINUTE)";
  552. }
  553. }
  554. }
  555. }
  556. else
  557. {
  558. String d = noArg;
  559. int argCount = 0;
  560. if (d.Contains('*'))
  561. {
  562. String[] dtsplit = d.Split('*');
  563. foreach (String dts in dtsplit)
  564. {
  565. dts.Trim();
  566. }
  567. if (argCount == 0)
  568. {
  569. date += "filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' AS DATETIME AND CAST'" + dtsplit[1] + "00' DATETIME";
  570. argCount++;
  571. }
  572. else
  573. {
  574. date += "OR filetransfers.time BETWEEN CAST '" + dtsplit[0] + "00' DATETIME AND CAST '" + dtsplit[1] + "00' DATETIME";
  575. }
  576.  
  577. }
  578. else
  579. {
  580. if (argCount == 0)
  581. {
  582. date += "filetransfers.time BETWEEN CAST '" + d + "00' DATETIME AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  583. argCount++;
  584. }
  585. else
  586. {
  587. date += " OR filetransfers.time BETWEEN '" + d + "00' AND DATE_ADD(CAST('" + d + "00' AS datetime), INTERVAL +5 MINUTE)";
  588. }
  589. }
  590. }
  591. }
  592.  
  593. }
  594. String args = "SELECT users.username, filetransfers.filename, filetransfers.receiverID, filetransfers.size, filetransfers.time FROM filetransfers INNER JOIN users ON users.id=filetransfers.userid WHERE ";
  595. int argcount = 0;
  596. if (sendersb)
  597. {
  598. if (argcount == 0)
  599. {
  600. args += senders;
  601. argcount++;
  602. }
  603. else
  604. {
  605. args += " AND " + senders;
  606. }
  607. }
  608. if (dateb)
  609. {
  610. if (argcount == 0)
  611. {
  612. args += date;
  613. argcount++;
  614. }
  615. else
  616. {
  617. args += " AND " + date;
  618. }
  619. }
  620. if (receiversb)
  621. {
  622. if (argcount == 0)
  623. {
  624. args += receivers;
  625. argcount++;
  626. }
  627. else
  628. {
  629. args += " AND " + receivers;
  630. }
  631. }
  632. if (fileinfob)
  633. {
  634. if (argcount == 0)
  635. {
  636. args += fileinfo;
  637. argcount++;
  638. }
  639. else
  640. {
  641. args += " AND " + fileinfo;
  642. }
  643. }
  644.  
  645. args += " ORDER BY filetransfers.time ASC;";
  646.  
  647. String data = null;
  648. Console.WriteLine(args);
  649. Console.WriteLine();
  650.  
  651. command = connection.CreateCommand();
  652. command.CommandText = args;
  653. reader = command.ExecuteReader();
  654.  
  655. while (reader.Read())
  656. {
  657. data += "(" + reader["time"].ToString() + ") Sender: " + reader["username"].ToString() + " || File: " + reader["filename"].ToString() + " || Size: " + reader["size"].ToString() + " Bytes" + Environment.NewLine;
  658. }
  659.  
  660. reader.Close();
  661.  
  662. return (data);
  663.  
  664. }
  665. catch (Exception ex)
  666. {
  667. Console.WriteLine(" >/log ERROR: " + ex.Message);
  668. return (null);
  669.  
  670. }
  671. }
  672.  
  673. public void fileTransfer(String userName, String toUser, String fileName, int fileSize)
  674. {
  675. command = connection.CreateCommand();
  676. command.CommandText = "SELECT id FROM users WHERE username='" + userName + "';";
  677. reader = command.ExecuteReader();
  678. reader.Read();
  679. String id = reader.GetValue(0).ToString(); // get id
  680. reader.Close();
  681.  
  682. command = connection.CreateCommand();
  683. command.CommandText = "SELECT id FROM users WHERE username='" + toUser + "';";
  684. reader = command.ExecuteReader();
  685. reader.Read();
  686. String recid = reader.GetValue(0).ToString(); // get id
  687. reader.Close();
  688.  
  689. command = connection.CreateCommand();
  690. command.CommandText = "INSERT INTO filetransfers(userid,size,filename,time,receiverID) VALUES('" + id + "','" + fileSize + "','" + fileName + "',NOW(),'" + recid + "');";
  691. command.ExecuteNonQuery();
  692. }
  693. }
  694. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!