forked from netkiller/netkiller.github.io
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathcoding.db.html
More file actions
executable file
·94 lines (84 loc) · 7.9 KB
/
coding.db.html
File metadata and controls
executable file
·94 lines (84 loc) · 7.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>13.3. Database</title><link rel="stylesheet" type="text/css" href="..//docbook.css" /><meta name="generator" content="DocBook XSL Stylesheets V1.78.1" /><meta name="keywords" content=", , , " /><link rel="home" href="../index.html" title="Netkiller DevOps 手札" /><link rel="up" href="coding.html" title="第 13 章 编码风格" /><link rel="prev" href="coding.string.html" title="13.2. String" /><link rel="next" href="../ops/index.html" title="部分 III. 运维篇(Operations)" /></head><body><a xmlns="" href="http://www.netkiller.cn/">Home</a> |
<a xmlns="" href="http://netkiller.github.io/">简体中文</a> |
<a xmlns="" href="http://netkiller.sourceforge.net/">繁体中文</a> |
<a xmlns="" href="/journal/index.html">杂文</a> |
<a xmlns="" href="/search.html">Search</a> |
<a xmlns="" href="http://netkiller-github-com.iteye.com/">ITEYE 博客</a> |
<a xmlns="" href="http://my.oschina.net/neochen/">OSChina 博客</a> |
<a xmlns="" href="https://www.facebook.com/bg7nyt">Facebook</a> |
<a xmlns="" href="http://cn.linkedin.com/in/netkiller/">Linkedin</a> |
<a xmlns="" href="mailto:netkiller@msn.com">Email</a><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">13.3. Database</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="coding.string.html">上一页</a> </td><th width="60%" align="center">第 13 章 编码风格</th><td width="20%" align="right"> <a accesskey="n" href="../ops/index.html">下一页</a></td></tr></table><hr /></div><table xmlns=""><tr><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=watch&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=fork&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&type=follow&count=true&size=large" height="30" width="240" frameborder="0" scrolling="0" style="width:240px; height: 30px;" allowTransparency="true"></iframe></td></tr></table><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="coding.db"></a>13.3. Database</h2></div></div></div><p>使用pdo_mysql替代mysql</p><p>错误的写法，通过字符串链接拼接sql语句极容易出现注入漏洞</p><pre class="screen">
$sql = "select * from table where id=".$id;
$sql = "select * from table where id='".$id."'";
$sql = "INSERT INTO fruit(name, colour) VALUES ('".$name."', '".$colour."')";
</pre><p>正确的写法</p><pre class="screen">
$sql = "select * from table where id=?";
$sql = "INSERT INTO fruit(name, colour) VALUES (?, ?)";
</pre><pre class="screen">
$sql = <<<____SQL
CREATE TABLE IF NOT EXISTS `ticket_hist` (
`tid` int(11) NOT NULL,
`trqform` varchar(40) NOT NULL,
`trsform` varchar(40) NOT NULL,
`tgen` datetime NOT NULL,
`tterm` datetime,
`tstatus` tinyint(1) NOT NULL
) ENGINE=ARCHIVE COMMENT='ticket archive';
____SQL;
</pre><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idm45956306852272"></a>13.3.1. 结果集使用注意事项</h3></div></div></div><p>返回数据库查询结果有几种形式</p><p>数组形式</p><pre class="screen">
Array
(
[0] => banana
[1] => yellow
)
Array
(
[NAME] => banana
[COLOUR] => yellow
)
</pre><p>对象形式</p><pre class="screen">
Object
(
Obj->NAME
Obj->COLOUR
)
</pre><p>正确的使用方式</p><pre class="screen">
print($row[name])
print($row->name)
</pre><p>错误的使用使方式</p><pre class="screen">
print($row[0])
</pre><p>避免使用 "*"查询，一会影响性能，二增加带宽开销</p><pre class="screen">
$sql = "select * from tab where status=0 limit 1";
</pre><p>如果程序使用$row[1]读取结果，有可能当数据库结构改变，增加字段，字段顺序发生变化，输出数据都会出错</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idm45956306852144"></a>13.3.2. 索引</h3></div></div></div><p>下面的例子，不会使用索引</p><pre class="screen">
$sql = "select id, name, created from tab where id != 100";
</pre><pre class="screen">
EXPLAIN select * from members where id != '1010'; 索引失效
EXPLAIN select count(*) from members where id != '1010'; 索引有效
</pre></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idm45956306846368"></a>13.3.3. 缓存</h3></div></div></div><pre class="screen">
</pre><p>下面的例子，数据不会缓存查询结果</p><pre class="screen">
$sql = "select id, name, created from tab where created=now()";
</pre></div></div><div xmlns="" id="disqus_thread"></div><script xmlns="" type="text/javascript">
/* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
//if(document.domain == 'netkiller.github.com'){
var disqus_shortname = 'netkiller'; // required: replace example with your forum shortname
//}else{
//var disqus_shortname = 'neochan';
//}
/* * * DON'T EDIT BELOW THIS LINE * * */
(function() {
var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
dsq.src = 'http://' + disqus_shortname + '.disqus.com/embed.js';
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
})();
</script><noscript xmlns="">Please enable JavaScript to view the <a href="http://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript><a xmlns="" href="http://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a><br xmlns="" /><script xmlns="" type="text/javascript" id="clustrmaps" src="//cdn.clustrmaps.com/map_v2.js?u=r5HG&d=9mi5r_kkDC8uxG8HuY3p4-2qgeeVypAK9vMD-2P6BYM"></script><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="coding.string.html">上一页</a> </td><td width="20%" align="center"><a accesskey="u" href="coding.html">上一级</a></td><td width="40%" align="right"> <a accesskey="n" href="../ops/index.html">下一页</a></td></tr><tr><td width="40%" align="left" valign="top">13.2. String </td><td width="20%" align="center"><a accesskey="h" href="../index.html">起始页</a></td><td width="40%" align="right" valign="top"> 部分 III. 运维篇(Operations)</td></tr></table></div><script xmlns="">
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-11694057-1', 'auto');
ga('send', 'pageview');
</script><script xmlns="" type="text/javascript">
var _bdhmProtocol = (("https:" == document.location.protocol) ? " https://" : " http://");
document.write(unescape("%3Cscript src='" + _bdhmProtocol + "hm.baidu.com/h.js%3F997cd4a7320a82d72cb74d179118f697' type='text/javascript'%3E%3C/script%3E"));
</script><script xmlns="" type="text/javascript" src="/js/q.js"></script></body></html>