端端舝

SQL奶件斥尼弁扑亦件
疏嗣瘋桻
1

本平奴伉氾奴
? 本平亙失卅袨颷
? 砩�仄卅中仇午毛卅今木卅中方丹卞﹜ 睡日井及�習互琌元日木
化中月袨颷
? 本平亙伉氾奴奶件扑犯件玄
? 砩�仄卅中仇午毛今木化仄引丹仇午ㄗ馴𨬢毛忳仃月ㄘ
? 砩�六內卞﹜本平亙失匹反卅中袨颷卞�月仇午ㄗ昜毛卅仁允ㄘ
? 本平亙失卅袨颷反本平亙伉氾奴奶件扑犯件玄互逃汜仄卅中袨颷午
反晟尹卅中
2

本平亙伉氾奴奶件扑犯件玄午Web扔奈田及
憝歹曰
? Web扔奈田公及手及互掩漲毛忳仃月�磁
? Web扔奈田井日由旦伐奈玉支弁伊斥永玄市奈玉霜堤
? SQL奶件斥尼弁扑亦件
? 㻢腔倰丟奈伙卞方月它奴伙旦覜�
? Web扔奈田互本平亙伉氾奴奶件扑犯件玄毛翑墿
? 傖曰井歹曰匹﹜𡧳尨啣尺𤩸五煋心毛今木月
? 弁伕旦扔奶玄伉弁巨旦玄白巧奈斥尼伉
? ID�\勻龰曰卞方月
? 本永扑亦件白奴弁本奈扑亦件
? 砩�仄卅中皿伕弘仿丞及灍俴
? 弁伕旦扔奶玄旦弁伉皿氾奴件弘
3

方仁丐月Web及�傖ㄗServlet)
? 方仁丐月�傖ˊ白伊奈丞伐奈弁
JavaEE
(glassfish/wildfly卅升ㄘ
Java
皿伕弘仿丞
(Servlet)
JSP白央奶伙
Servlet倰失奈平氾弁民乓
RDBMS
(Oarcle,mysql,postg
reSQL卅升ㄘ
JDBC
皮仿它扒
4

扔件皿伙失皿伉弗奈扑亦件 SampleBBS
�眢𡧳尨啣失皿伉 SampleBBS
辻夔
伕弘奶件允月午芘詨褫夔
伕弘奶件仄卅仃木壬𨈘坰�笊及心褫夔
正奶玄伙𨈘坰賒醱
sss午⻌木化𨈘坰示
正件毛挹允
𨈘坰磐彆桶尨
5

SampleBBS及𨈘坰辻夔及衙猁
<form action=
※<%= response.encodeURL(§/SampleBBS/SearchArticles※) %>§
method="GET">正奶玄伙𨈘坰
<input type="text" name="word" value="${param.word}">
<input type="submit" value="𨈘坰">
</form>
try(Statement st=con.createStatement();){
String sql = ※SELECT * FROM articles A JOIN users U ON U.id=user_id WHERE title LIKE '%" + word +"%' ORDER BY 1";
ResultSet rs = st.executeQuery(sql); // 𨈘坰磐彆毛伉旦玄卞跡慮
while (rs.next()) {
articles.add(new Article(
rs.getInt("id"),rs.getInt("user_id"), rs.getString("email"), rs.getString("title"), rs.getString("body")));
}
}
𨈘坰賒醱 JSP
𨈘坰 Servlet
GET http://hostname/SampleBBS/SearchArticles?word=※sss§
SQL恅及釬傖卞String左皮
斥尼弁玄及磐磁毛妏丹
6

犯奈正矛奈旦氾奈皮伙�婖
+----+---------+--------+-----------------------------------------+
| id | user_id | title | body |
+----+---------+--------+-----------------------------------------+
| 1 | 2 | Titl1 | This is a contents of Article 1 |
| 2 | 2 | Titl2 | This is a contents of Article 2 of hiro |
| 3 | 1 | Titl3 | This is a contents of Article 3 of hata |
| 4 | 1 | Title4 | This is a contents of Article 4 of hata |
| 5 | 1 | sss | dfdsfsa |
| 6 | 2 | tt | tt |
+----+---------+--------+-----------------------------------------+
+----+--------------+----------+-----------------------+
| id | name | pass | email |
+----+--------------+----------+-----------------------+
| 1 | hata | hatahata | h.hata@olt.tokyo |
| 2 | hiro | hiro | test@ntt.com |
| 3 | hata2 | hahata | hata@olt.tokyo |
| 6 | rrr | 8888 | hata@qc5.so-net.ne.jp |
+----+--------------+----------+-----------------------+
𨈘坰灍俴及凶戶卞ㄡ勾及桶毛磐磁仄化﹜ㄢ勾及市仿丞毛龰曰堤允
SELECT * FROM articles A JOIN users U ON U.id=user_id WHERE
title LIKE '%sss%' ORDER BY 1
+----+---------+-------+---------+----+------+----------+------------------+
| id | user_id | title | body | id | name | pass | email |
+----+---------+-------+---------+----+------+----------+------------------+
| 5 | 1 | sss | dfdsfsa | 1 | hata | hatahata | h.hata@olt.tokyo |
+----+---------+-------+---------+----+------+----------+------------------+
芘詨�岈午交奈扒奪燴及ㄡ繹及桶匹�傖今木月
芘詨氪杻隅及凶戶卞﹜交奈扒ID匹伉伊奈扑亦件今木月
7

由旦伐奈玉及霜堤
𨈘坰磐彆桶尨
' UNION SELECT 0,0,name,pass,0,'a','a','a'
from users where name like '%
𨈘坰�卞SQL恅及剿⑵毛⻌薯允月
正奶玄伙及窒煦卞交奈扒ID
掛恅及窒煦卞由旦伐奈玉互桶尨今木化中月
8

SELECT﹜UNION午JOIN卞勾中化
SELECT:ㄠ勾及桶井日�e及陔仄中桶毛釬曰堤允
JOIN:ㄡ勾及桶毛筵源砃卞�K屯化�e及桶毛釬曰堤允
UNION:ㄡ勾及桶毛磬源砃卞�K屯化�e及桶毛釬曰堤允
A B
A B+ A B
A B+ A
B
SELECT
JOIN
UNION
9

祥淏SQL恅及�釬
卅兮由旦伐奈玉互桶尨今木凶及井ˋ
SELECT * FROM articles A JOIN users U ON U.id=user_id WHERE title LIKE '%'
UNION SELECT 0,0,name,pass,0,'a','a','a' from users where name like '%%' ORDER BY 1
+----+---------+-------+---------+----+------+----------+------------------+
+----+---------+-------+---------+----+------+----------+------------------+
| 5 | 1 | sss | dfdsfsa | 1 | hata | hatahata | h.hata@olt.tokyo |
+---+---+--------------+----------+---+---+---+---+
| 0 | 0 | name | pass | 0 | a | a | a |
+---+---+--------------+----------+---+---+---+---+
| 0 | 0 | hata | hatahata | 0 | a | a | a |
| 0 | 0 | hiro | hiro | 0 | a | a | a |
| 0 | 0 | hata2 | hahata | 0 | a | a | a |
| 0 | 0 | hata2 | hahata | 0 | a | a | a |
| 0 | 0 | hata2 | hahata | 0 | a | a | a |
1�醴及SELECT匹汜傖今木凶桶ㄗ手午手午砩�仄化中凶桶ˊㄧ市仿丞毛厥切﹜丹切ㄢ市仿丞毛妏丹ㄘ
ㄡ�醴及SELECT匹汜傖今木凶桶
+----+---------+---------+------------+----+------+----------+-----------+
+----+---------+---------+------------+----+------+----------+-----------+
| 0 | 0 | hata | hatahata | 0 | a | a | a |
| 0 | 0 | hiro | hiro | 0 | a | a | a |
| 0 | 0 | hata2 | hahata | 0 | a | a | a |
| 0 | 0 | rrr | 8888 | 0 | a | a | a |
| 0 | 0 | trew | 5555 | 0 | a | a | a |
| 0 | 0 | hhaat | 11111 | 0 | a | a | a |
UNION
10

SQL奶件斥尼弁扑亦件及��g卅滅砦習
? 𨈘坰�卅升卞⻌木日木凶恅趼蹈毛SQL恅及珨窒午仄化賤�仄凶
仇午互埻秪
? ⻌薯今木凶恅趼蹈卞﹜＊(扑件弘伙弁巧奈玄恅趼ㄘ互漪引木化中凶日巨旦弗奈皿允月
? ＊ => ?＊ ※=>?§ #=>##
SELECT * FROM articles A JOIN users U ON U.id=user_id WHERE title LIKE '%
?' UNION SELECT 0,0,name,pass,0,?'a?',?'a?',?'a?' from users where name like?'%
%' ORDER BY 1
⻌薯今木凶恅趼蹈反﹜SQL恅午仄化賤�今木卅中 11

SQL奶件斥尼弁扑亦件�習
? 犯奈正矛奈旦撮胍氪午仄化匹五月仇午
? 皿伕弘仿穴奈午仄化匹五月仇午
? 生永玄伐奈弁撮胍氪午仄化匹五月仇午
12

犯奈正矛奈旦�習
? 羥ピ卅痜癹及葆迵
? 市仿丞仍午卞失弁本旦痜癹及偞隅匹五月犯奈正矛奈旦及�磁
?扔奈皮伊永玄卞反由旦伐奈玉支弁伊斥永玄市奈玉ロ�毛
漪戈市仿丞卞反失弁本旦痜癹毛厥凶卅中犯奈正矛奈旦伕
弘奶件ID毛葆迵允月
? 桶仍午卞失弁本旦痜癹毛偞隅匹五月犯奈正矛奈旦
?由旦伐奈玉支弁伊斥永玄市奈玉ロ�卅升反﹜杻�e及桶毛
釬勻化扔奈皮伊永玉井日反失弁本旦匹五卅中方丹卞允月
13

皿伕弘仿立件弘�習
String sql = "SELECT * FROM articles A JOIN users U ON U.id=user_id"+" WHERE title LIKE '%" + word +"%' ORDER BY 1";
ResultSet rs = st.executeQuery(sql);
String sql = "SELECT * FROM articles JOIN users ON user_id=users.id"+" WHERE title LIKE ? ORDER BY 1";
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, "%" + word + "%");
ResultSet rs = st.executeQuery();
党淏今木凶戊奈玉
晟惤匹蚚砩今木化中月SQL奶件斥尼弁扑亦件�習辻夔毛瞳蚚允月
String及磐磁匹反卅仁﹜PreparedStatement弁仿旦及setString毛妏丹
ㄗ蛁砩ㄘPreparedStatement毛妏尹壬中中及匹反卅仁﹜
皿伊奈旦石伙母卞�毛本永玄允月及卞setString毛妏丹仇午匹場戶化�習卞卅月
杻忷恅趼毛巨旦弗奈皿允月﹝凶分仄﹜忒�匹反卅仁晟惤仍午卞蚚砩今木化中月
PreparedStatement弁仿旦毛妏丹
14

生永玄伐奈弁�匹及�習
毯ʃ俶毛厥勾
Web扔奈田
WAF
GET http://hostname/SampleBBS/SearchArticles?word=※sss§
GET http://hostname/SampleBBS/SearchArticles?word=※' UNION SELECT
0,0,name,pass,0,'a','a','a' from users where name like '%§
X
WAF卞偞隅匹五月禾伉扑奈瞰
HTTP伉弁巨旦玄卞§SELECT§互漪引木月午籵陓殑剿
囡謎卅交奈扒
馴𨬢氪
WAF毛妏丹
ㄗWAFㄩWeb Application Firewall :
HTTP及笢毛�化籵陓及浩籵毛瓚剿允月白央奶乩它巧奈伙ㄘ
15

引午戶
? SQL奶件斥尼弁扑亦件午反
? Web賒醱及⻌薯犯奈正卞SQL恅ㄗ及珨窒ㄘ互⻌薯仄化砩�仄卅中�釬
毛桸仁馴𨬢
? SQL奶件斥尼弁扑亦件及爍哏
? 蕎允屯五犯奈正及穢䪸
? 犯奈正矛奈旦及勹𠧧(DROP恅支﹜DELETE恅及髦⻌ㄘ
? �習
? 犯奈正矛奈旦失弁本旦氪尺及羥ピ卅痜癹及葆迵
? 皿伕弘仿立件弘卞云仃月SQL恅汜傖卞PreparedStatement弁仿旦毛妏丹
? WAF卞方月SQL恅髦⻌及HTTP伉弁巨旦玄及齬壺
? 駙磁�習及笭猁俶
? 珨勾及�習匹反﹜馴𨬢�及筳餜卞反袚𣶹匹五卅中
? 政媆萸匹綎�午佷歹木月最僅及恚杅�習互蔚懂及馴𨬢卞衄� 16

端端舝

釦紮郭奶件扑?尼弁扑亦件

More Related Content

釦紮郭奶件扑?尼弁扑亦件

Editor's Notes