PHP防止SQL注入的一种思路

2014-7-21 liyangweb.com 李杨 PHP

问题描述:

  如果用户输入的数据在未经处理的情况下插入到一条SQL查询语句,那么应用将很可能遭受到SQL注入攻击,正如下面的例子:

1
2
3
$unsafe_variable=$_POST['user_input'];
 
mysql_query("INSERT INTO `table` (`column`) VALUES ('".$unsafe_variable."')");

  因为用户的输入可能是这样的:

1
value'); DROP TABLE table;--

  那么SQL查询将变成如下:

1
INSERTINTO`table` (`column`)VALUES('value');DROPTABLEtable;--')

  应该采取哪些有效的方法来防止SQL注入?

 最佳回答(来自Theo):

  使用预处理语句和参数化查询。预处理语句和参数分别发送到数据库服务器进行解析,参数将会被当作普通字符处理。这种方式使得攻击者无法注入恶意的SQL。 你有两种选择来实现该方法:

  1、使用PDO:

1
2
3
4
5
6
7
$stmt=$pdo->prepare('SELECT * FROM employees WHERE name = :name');
 
$stmt->execute(array('name'=>$name));
 
foreach($stmtas$row) {
    // do something with $row
}

  2、使用mysqli:

1
2
3
4
5
6
7
8
9
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);
 
$stmt->execute();
 
$result = $stmt->get_result();
while($row = $result->fetch_assoc()) {
    // do something with $row
}

 PDO

  注意,在默认情况使用PDO并没有让MySQL数据库执行真正的预处理语句(原因见下文)。为了解决这个问题,你应该禁止PDO模拟预处理语句。一个正确使用PDO创建数据库连接的例子如下:

1
2
3
4
$dbConnection=newPDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8','user','pass');
 
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  在上面的例子中,报错模式(ATTR_ERRMODE)并不是必须的,但建议加上它。这样,当发生致命错误(Fatal Error)时,脚本就不会停止运行,而是给了程序员一个捕获PDOExceptions的机会,以便对错误进行妥善处理。 然而,第一个setAttribute()调用是必须的,它禁止PDO模拟预处理语句,而使用真正的预处理语句,即有MySQL执行预处理语句。这能确保语句和参数在发送给MySQL之前没有被PHP处理过,这将使得攻击者无法注入恶意SQL。了解原因,可参考这篇博文:PDO防注入原理分析以及使用PDO的注意事项。 注意在老版本的PHP(<5.3.6),你无法通过在PDO的构造器的DSN上设置字符集,参考:silently ignored the charset parameter

 解析

  当你将SQL语句发送给数据库服务器进行预处理和解析时发生了什么?通过指定占位符(一个?或者一个上面例子中命名的 :name),告诉数据库引擎你想在哪里进行过滤。当你调用execute的时候,预处理语句将会与你指定的参数值结合。 关键点就在这里:参数的值是和经过解析的SQL语句结合到一起,而不是SQL字符串。SQL注入是通过触发脚本在构造SQL语句时包含恶意的字符串。所以,通过将SQL语句和参数分开,你防止了SQL注入的风险。任何你发送的参数的值都将被当作普通字符串,而不会被数据库服务器解析。回到上面的例子,如果$name变量的值为 ’Sarah’; DELETE FROM employees ,那么实际的查询将是在 employees 中查找 name 字段值为 ’Sarah’; DELETE FROM employees 的记录。 另一个使用预处理语句的好处是:如果你在同一次数据库连接会话中执行同样的语句许多次,它将只被解析一次,这可以提升一点执行速度。 如果你想问插入该如何做,请看下面这个例子(使用PDO):

1
2
3
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');
 
$preparedStatement->execute(array('column'=> $unsafeValue));

  原文链接: StackOverflow   翻译: 伯乐在线 - rokety

标签: mysql PHP sql注入

评论:

lovehosaga
2019-01-25 04:56
<a href="http://www.bladeid.com/nike-air-roshe-run-hyperfuse-rojo-kurpese">nike air roshe run hyperfuse rojo</a> <a href="http://www.bakomoms.com/air-jordan-retro-10-stealth-herre-kondisko-billig-chaussuresa">air jordan retro 10 stealth herre kondisko billig</a> <a href="http://www.macrackmount.com/ua-clutchfit-drive-low-r%C3%B8d-quarter-kurpesg">ua clutchfit drive low r酶d quarter</a> <a href="http://www.martinsedacca.com/nike-air-huarache-gr%C3%A5-royal-lyser%C3%B8d-schuhee">nike air huarache gr氓 royal lyser酶d</a> <a href="http://www.maxibymaxi.com/dam%C3%A4nner-nike-shox-tl3-alle-schwarz-obuvg">dam盲nner nike shox tl3 alle schwarz</a> <a href="http://www.slatervision.com/air-jordan-2017-gr%C3%A5-kurpesf">air jordan 2017 gr氓</a>
lovehosaga http://www.lovehosaga.com/
dfarrie
2019-01-20 11:29
<a href="http://www.acforlessllc.com/nike-air-max-2019-green-uk-shoeso">nike air max 2019 green uk</a> <a href="http://www.thehodostore.com/jordan-retro-7-rose-and-gris-kurpesb">jordan retro 7 rose and gris</a> <a href="http://www.gandoff.com/adidas-equipment-running-suport-eqt-cq2991-zapatost">adidas equipment running suport eqt cq2991</a> <a href="http://www.langbenlang.com/converse-femme-all-star-blanche-shoesz">converse femme all star blanche</a> <a href="http://www.glennloovens.com/adidas-zx-flux-p%C3%BArpura-rosado-schuheb">adidas zx flux p煤rpura rosado</a> <a href="http://www.kamilotomotiv.com/mens-nike-cleveland-browns-26-derrick-kindred-limited-olivecamo-2017-salute-to-service-nfl-jersey-nflu">mens nike cleveland browns 26 derrick kindred limited olivecamo 2017 salute to service nfl jersey</a>
dfarrie http://www.dfarrie.com/
bll696gw
2019-01-11 14:03
<a href="http://www.k3htroya.com/air-jordan-12-deep-royal-bleu-quartz-schuhef">air jordan 12 deep royal bleu quartz</a> <a href="http://www.y7dbpccn.com/nike-lunarglide-6-nero-femminile-gituttio-butye">nike lunarglide 6 nero femminile gituttio</a> <a href="http://www.z9b51ckf.com/air-jordan-31-rojo-butya">air jordan 31 rojo</a> <a href="http://www.vt310g2y.com/nike-air-max-thea-for-bon-march%C3%A9-obuvb">nike air max thea for bon march茅</a> <a href="http://www.dhvwolje.com/nike-air-max-1-obsidian-slate-bl%C3%A5-hvit-scarped">nike air max 1 obsidian slate bl氓 hvit</a> <a href="http://www.v870k5rd.com/mennns-nike-air-max-1-gr%C3%B8nn-gull-schuhef">mennns nike air max 1 gr酶nn gull</a>
bll696gw http://www.bll696gw.com/
sdsremote
2019-01-07 07:24
<a href="http://www.tomscave.com/air-jordan-retro-13-vert-gris-kurpesf">air jordan retro 13 vert gris</a> <a href="http://www.veronicaloman.com/vibram-fivefingers-kso-verde-nero-schuheg">vibram fivefingers kso verde nero</a> <a href="http://www.casagranderox.com/nike-hypervenom-phantom-ii-ag-soccer-boots-high-argento-arancia-nero-schuheb">nike hypervenom phantom ii ag soccer boots high argento arancia nero</a> <a href="http://www.elitoyreviews.com/herren-nike-air-max-tn-rosa-orange-chaussurese">herren nike air max tn rosa orange</a> <a href="http://www.grassmannword.com/nike-lunartempo-azul-lagoon-butyg">nike lunartempo azul lagoon</a> <a href="http://www.harryselite.com/adidas-tubular-runner-primeknit-s%C3%B8lv-svart-scarpea">adidas tubular runner primeknit s酶lv svart</a>
sdsremote http://www.sdsremote.com/
eventyrshop
2019-01-02 20:28
<a href="http://www.cohenhypnosis.com/nike-huarache-ultra-tout-noir-up-obuvg">nike huarache ultra tout noir up</a> <a href="http://www.paulabandura.com/maschio-nike-free-5.0-premium-in-esecuzione-scarpe-chaussuresa">maschio nike free 5.0 premium in esecuzione scarpe</a> <a href="http://www.cartsdk.com/jordan-3-infrarouge-toronto-butya">jordan 3 infrarouge toronto</a> <a href="http://www.breachurch.com/reekbok-revenge-plus-vintage-p%C3%BArpura-blanco-obuvc">reekbok revenge plus vintage p煤rpura blanco</a> <a href="http://www.facommobiles.com/asics-gel-kayano-20-hombre-rouge-scarpea">asics gel kayano 20 hombre rouge</a> <a href="http://www.sarkariresurt.com/nike-internationalist-minzgr%C3%BCn-scarpef">nike internationalist minzgr眉n</a>
eventyrshop http://www.eventyrshop.com/
hanayaflowers
2018-12-30 18:34
<a href="http://www.cheapcoatsfr.com/nike-kd-8-ext-negro-keys-kurpesa">nike kd 8 ext negro keys</a> <a href="http://www.mylinkstv.com/air-jordan-13-bred-2016-sneakerb">air jordan 13 bred 2016</a> <a href="http://www.phxfam.com/nike-air-jordan-4-retro-silver-anniversary-shoesg">nike air jordan 4 retro silver anniversary</a> <a href="http://www.shanibazaar.com/villanova-knit-hats">villanova knit</a> <a href="http://www.imannequins.com/nike-huarache-air-yellow-sneakerf">nike huarache air yellow</a> <a href="http://www.irentevent.com/adidas-zx-750-gr%C3%B8n-parts-obuvc">adidas zx 750 gr酶n parts</a>
hanayaflowers http://www.hanayaflowers.com/
hepalmak
2018-12-29 11:08
<a href="http://www.brutalistmap.com/mens-north-face-down-jacket-clearance-northfacec">mens north face down jacket clearance</a> <a href="http://www.paulawooley.com/prada-quilted-nylon-bag-pradar">prada quilted nylon bag</a> <a href="http://www.hagertools.com/nike-air-zoom-pegasus-32-black-black-skos">nike air zoom pegasus 32 black black</a> <a href="http://www.signalsvideo.com/mlb-jerseys-san-diego-padres-44-jake-peavy-home-white-jerseys-nfld">mlb jerseys san diego padres 44 jake peavy home white jerseys</a> <a href="http://www.blainefxb.com/bon-march%C3%A9-nike-air-force-1-flyknit-high-hommes-argent-skod">bon march茅 nike air force 1 flyknit high hommes argent</a> <a href="http://www.linamauri.com/burberry-check-print-silk-scarf-scarfr">burberry check print silk scarf</a>
[url=http://www.hepalmak.com/]hepalmak[/url]
orderollie
2018-12-28 14:35
<a href="http://www.khalataxi.com/adidas-adizero-f50-trx-lyser%C3%B8d-brun-kurpesb">adidas adizero f50 trx lyser酶d brun</a> <a href="http://www.miaclearbras.com/nike-air-foamposite-one-by-sierato-zapatost">nike air foamposite one by sierato</a> <a href="http://www.divyangbazar.com/all-black-converse-high-tops-womens-shoesz">all black converse high tops womens</a> <a href="http://www.dixiepundit.com/kvinders-nike-roshe-run-triangle-gr%C3%A5-gul-schuheb">kvinders nike roshe run triangle gr氓 gul</a> <a href="http://www.ifshaildamage.com/mike-ditka-womens-limited-whitepink-jersey-nike-nfl-chicago-bears-rush-fashion-89-nflu">mike ditka womens limited whitepink jersey nike nfl chicago bears rush fashion 89</a> <a href="http://www.nanterrelec.com/nike-air-huarache-full-black-skou">nike air huarache full black</a>
orderollie http://www.orderollie.com/

发表评论:

Powered by emlog 冀ICP备13011830号-1