时间:2021-07-01 10:21:17 帮助过:22人阅读
程序mysqlite3.c如下:
View Code
- <span style="color: #008080"> 1</span> #include <sqlite3.h>
- <span style="color: #008080"> 2</span> #include <stdio.h>
- <span style="color: #008080"> 3</span> #include <<span style="color: #0000ff">string</span>.h>
- <span style="color: #008080"> 4</span> #include <span style="color: #800000">"</span><span style="color: #800000">md5.h</span><span style="color: #800000">"</span>
- <span style="color: #008080"> 5</span>
- <span style="color: #008080"> 6</span> <span style="color: #008000">/*</span><span style="color: #008000">定义此宏时使用回调函数查询否则只是用非回调函数查询</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 7</span> <span style="color: #008000">//</span><span style="color: #008000">#define CALLBACK </span>
- <span style="color: #008080"> 8</span>
- <span style="color: #008080"> 9</span> <span style="color: #008000">/*</span><span style="color: #008000">定义创建表格指令IF not EXISTS:不存在,AUTOINCREMENT:自动增加主键,not NULL:不能为空</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 10</span> <span style="color: #0000ff">#define</span> CREATE "create table IF not EXISTS passwd(id integer primary key AUTOINCREMENT,username text not NULL,password text not NULL)"
- <span style="color: #008080"> 11</span> <span style="color: #008000">/*</span><span style="color: #008000">定义查询数据指令</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 12</span> <span style="color: #0000ff">#define</span> SELECT "select * from passwd where username=‘%s‘ and password=‘%s‘"
- <span style="color: #008080"> 13</span> <span style="color: #008000">/*</span><span style="color: #008000">定义插入数据指令</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 14</span> <span style="color: #0000ff">#define</span> INSERT "insert into passwd(username,password) values(‘%s‘,‘%s‘)"
- <span style="color: #008080"> 15</span>
- <span style="color: #008080"> 16</span> <span style="color: #008000">/*</span><span style="color: #008000">如果查询到多行数据, 那么这个函数就会调用多次(每一行调用一次)</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 17</span> <span style="color: #0000ff">int</span> callback(<span style="color: #0000ff">void</span> *arg, <span style="color: #0000ff">int</span> col, <span style="color: #0000ff">char</span> **value, <span style="color: #0000ff">char</span> **<span style="color: #000000">name)
- </span><span style="color: #008080"> 18</span> <span style="color: #000000">{
- </span><span style="color: #008080"> 19</span> <span style="color: #0000ff">int</span> i=<span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080"> 20</span> <span style="color: #0000ff">for</span>(i=<span style="color: #800080">0</span>;i<col; i++<span style="color: #000000">)
- </span><span style="color: #008080"> 21</span> <span style="color: #000000"> {
- </span><span style="color: #008080"> 22</span> printf(<span style="color: #800000">"</span><span style="color: #800000">%s\t</span><span style="color: #800000">"</span><span style="color: #000000">, value[i]);
- </span><span style="color: #008080"> 23</span> <span style="color: #000000"> }
- </span><span style="color: #008080"> 24</span> printf(<span style="color: #800000">"</span><span style="color: #800000">\n</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 25</span> <span style="color: #0000ff">return</span> <span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080"> 26</span> <span style="color: #000000">}
- </span><span style="color: #008080"> 27</span>
- <span style="color: #008080"> 28</span>
- <span style="color: #008080"> 29</span> <span style="color: #0000ff">int</span> main(<span style="color: #0000ff">void</span><span style="color: #000000">)
- </span><span style="color: #008080"> 30</span> <span style="color: #000000">{
- </span><span style="color: #008080"> 31</span>
- <span style="color: #008080"> 32</span> <span style="color: #008000">//</span><span style="color: #008000">1.打开数据库</span>
- <span style="color: #008080"> 33</span> sqlite3 *ppdb =<span style="color: #000000"> NULL;
- </span><span style="color: #008080"> 34</span> <span style="color: #0000ff">int</span> ret = sqlite3_open(<span style="color: #800000">"</span><span style="color: #800000">./passwd</span><span style="color: #800000">"</span>, &ppdb); <span style="color: #008000">/*</span><span style="color: #008000">我们之前要先创建一个名字叫passwd的数据库</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 35</span> <span style="color: #0000ff">if</span>(ret !=<span style="color: #000000"> SQLITE_OK)
- </span><span style="color: #008080"> 36</span> <span style="color: #000000"> {
- </span><span style="color: #008080"> 37</span> perror(<span style="color: #800000">"</span><span style="color: #800000">open fail</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 38</span> <span style="color: #0000ff">return</span> -<span style="color: #800080">1</span><span style="color: #000000">;
- </span><span style="color: #008080"> 39</span> <span style="color: #000000"> }
- </span><span style="color: #008080"> 40</span> sqlite3_exec(ppdb,CREATE,NULL,NULL,NULL); <span style="color: #008000">/*</span><span style="color: #008000">创建一个表格</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 41</span> <span style="color: #0000ff">char</span> temp[<span style="color: #800080">32</span><span style="color: #000000">];
- </span><span style="color: #008080"> 42</span> <span style="color: #0000ff">char</span> temp1[<span style="color: #800080">32</span><span style="color: #000000">];
- </span><span style="color: #008080"> 43</span> <span style="color: #0000ff">char</span> insert[strlen(temp)+strlen(temp1)+<span style="color: #800080">200</span>]; <span style="color: #008000">/*</span><span style="color: #008000">这里的数组要给大一点,因为等下加密的时候会得到一串很长的数据</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 44</span> printf(<span style="color: #800000">"</span><span style="color: #800000">please input your username:</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 45</span> scanf(<span style="color: #800000">"</span><span style="color: #800000">%s</span><span style="color: #800000">"</span><span style="color: #000000">,temp);
- </span><span style="color: #008080"> 46</span> printf(<span style="color: #800000">"</span><span style="color: #800000">please input your password:</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 47</span> scanf(<span style="color: #800000">"</span><span style="color: #800000">%s</span><span style="color: #800000">"</span><span style="color: #000000">,temp1);
- </span><span style="color: #008080"> 48</span> MD5Str(temp1,temp1);<span style="color: #008000">/*</span><span style="color: #008000">把输入的密码使用md5加密存储在数据库表格中</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 49</span> printf(<span style="color: #800000">"</span><span style="color: #800000">1111\n</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 50</span>
- <span style="color: #008080"> 51</span>
- <span style="color: #008080"> 52</span> sprintf(insert,INSERT,temp,temp1);<span style="color: #008000">/*</span><span style="color: #008000">打包数据,准备插入到表格中</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 53</span>
- <span style="color: #008080"> 54</span> <span style="color: #000000"> sqlite3_exec(ppdb,insert,NULL,NULL,NULL);
- </span><span style="color: #008080"> 55</span>
- <span style="color: #008080"> 56</span>
- <span style="color: #008080"> 57</span> <span style="color: #0000ff">char</span> username[<span style="color: #800080">32</span><span style="color: #000000">];
- </span><span style="color: #008080"> 58</span> <span style="color: #0000ff">char</span> password[<span style="color: #800080">33</span><span style="color: #000000">];
- </span><span style="color: #008080"> 59</span> printf(<span style="color: #800000">"</span><span style="color: #800000">input username:</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 60</span> scanf(<span style="color: #800000">"</span><span style="color: #800000">%s</span><span style="color: #800000">"</span><span style="color: #000000">,username);
- </span><span style="color: #008080"> 61</span> printf(<span style="color: #800000">"</span><span style="color: #800000">input password:</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 62</span> scanf(<span style="color: #800000">"</span><span style="color: #800000">%s</span><span style="color: #800000">"</span><span style="color: #000000">,password);
- </span><span style="color: #008080"> 63</span>
- <span style="color: #008080"> 64</span> MD5Str(password,password);<span style="color: #008000">/*</span><span style="color: #008000">把输入的密码使用md5加密之后与数据库表格中的密码匹对</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 65</span> <span style="color: #0000ff">char</span> sql[strlen(SELECT)+strlen(username)+<span style="color: #000000">strlen(password)];
- </span><span style="color: #008080"> 66</span>
- <span style="color: #008080"> 67</span> <span style="color: #008000">/*</span><span style="color: #008000">打包一个字符串,将SELECT字符串放到sql中,username和password这两个变量放大SELECT中</span><span style="color: #008000">*/</span>
- <span style="color: #008080"> 68</span> <span style="color: #000000"> sprintf(sql,SELECT,username,password);
- </span><span style="color: #008080"> 69</span>
- <span style="color: #008080"> 70</span>
- <span style="color: #008080"> 71</span> <span style="color: #000000"> #ifdef CALLBACK
- </span><span style="color: #008080"> 72</span> <span style="color: #008000">//</span><span style="color: #008000">回调查询</span>
- <span style="color: #008080"> 73</span> <span style="color: #0000ff">char</span> *selectsql = <span style="color: #800000">"</span><span style="color: #800000">select * from myname</span><span style="color: #800000">"</span><span style="color: #000000">;
- </span><span style="color: #008080"> 74</span> ret =<span style="color: #000000"> sqlite3_exec(ppdb, selectsql, callback, NULL, NULL);
- </span><span style="color: #008080"> 75</span> <span style="color: #0000ff">if</span>(ret !=<span style="color: #000000"> SQLITE_OK)
- </span><span style="color: #008080"> 76</span> <span style="color: #000000"> {
- </span><span style="color: #008080"> 77</span> perror(<span style="color: #800000">"</span><span style="color: #800000">create fail</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 78</span> <span style="color: #000000"> sqlite3_close(ppdb);
- </span><span style="color: #008080"> 79</span> <span style="color: #0000ff">return</span> -<span style="color: #800080">1</span><span style="color: #000000">;
- </span><span style="color: #008080"> 80</span> <span style="color: #000000"> }
- </span><span style="color: #008080"> 81</span>
- <span style="color: #008080"> 82</span> <span style="color: #008000">//</span><span style="color: #008000">非回调查询</span>
- <span style="color: #008080"> 83</span> <span style="color: #0000ff">#else</span>
- <span style="color: #008080"> 84</span> <span style="color: #0000ff">char</span> **result =<span style="color: #000000"> NULL;
- </span><span style="color: #008080"> 85</span> <span style="color: #0000ff">int</span> row = <span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080"> 86</span> <span style="color: #0000ff">int</span> col = <span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080"> 87</span> <span style="color: #0000ff">char</span> *error =<span style="color: #000000"> NULL;
- </span><span style="color: #008080"> 88</span> ret = sqlite3_get_table(ppdb,sql,&result,&row,&col,&<span style="color: #000000">error);
- </span><span style="color: #008080"> 89</span> <span style="color: #0000ff">if</span>(ret !=<span style="color: #000000"> SQLITE_OK)
- </span><span style="color: #008080"> 90</span> <span style="color: #000000"> {
- </span><span style="color: #008080"> 91</span> perror(<span style="color: #800000">"</span><span style="color: #800000">get table fail</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080"> 92</span> <span style="color: #0000ff">return</span> -<span style="color: #800080">1</span><span style="color: #000000">;
- </span><span style="color: #008080"> 93</span> <span style="color: #000000"> }
- </span><span style="color: #008080"> 94</span>
- <span style="color: #008080"> 95</span> <span style="color: #0000ff">int</span> i=<span style="color: #800080">0</span>, j=<span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080"> 96</span> <span style="color: #0000ff">for</span>(i=<span style="color: #800080">0</span>;i<row+<span style="color: #800080">1</span>;i++<span style="color: #000000">)
- </span><span style="color: #008080"> 97</span> <span style="color: #000000"> {
- </span><span style="color: #008080"> 98</span> <span style="color: #0000ff">for</span>(j=<span style="color: #800080">0</span>;j<col;j++<span style="color: #000000">)
- </span><span style="color: #008080"> 99</span> <span style="color: #000000"> {
- </span><span style="color: #008080">100</span> printf(<span style="color: #800000">"</span><span style="color: #800000">%s\t</span><span style="color: #800000">"</span>,result[j+i*<span style="color: #000000">col]);
- </span><span style="color: #008080">101</span> <span style="color: #000000"> }
- </span><span style="color: #008080">102</span> printf(<span style="color: #800000">"</span><span style="color: #800000">\n</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080">103</span> <span style="color: #000000"> }
- </span><span style="color: #008080">104</span>
- <span style="color: #008080">105</span> <span style="color: #0000ff">if</span>(row > <span style="color: #800080">0</span>) <span style="color: #008000">/*</span><span style="color: #008000">数据匹配成功</span><span style="color: #008000">*/</span>
- <span style="color: #008080">106</span> printf(<span style="color: #800000">"</span><span style="color: #800000">checked OK\n</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080">107</span> <span style="color: #0000ff">else</span> <span style="color: #008000">/*</span><span style="color: #008000">数据匹配失败</span><span style="color: #008000">*/</span>
- <span style="color: #008080">108</span> printf(<span style="color: #800000">"</span><span style="color: #800000">fail\n</span><span style="color: #800000">"</span><span style="color: #000000">);
- </span><span style="color: #008080">109</span> sqlite3_free_table(result);<span style="color: #008000">//</span><span style="color: #008000">释放结果</span>
- <span style="color: #008080">110</span> <span style="color: #0000ff">#endif</span>
- <span style="color: #008080">111</span>
- <span style="color: #008080">112</span>
- <span style="color: #008080">113</span> <span style="color: #000000"> sqlite3_close(ppdb);
- </span><span style="color: #008080">114</span> <span style="color: #0000ff">return</span> <span style="color: #800080">0</span><span style="color: #000000">;
- </span><span style="color: #008080">115</span> }
我们先要创建一个名字叫passwd数据库,如果不懂创建可以看看我的这篇文章:linux数据库环境搭建
- sqlite3 passwd
接着我们编译程序
- gcc -o mysqlite3 mysqlite3.c -lsqlite3 -lmd5
运行结果如下:
linux数据库中使用MD5加密
标签:不能 -shared 结果 文章 打包 target select rcm user