Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
github.com/pinzolo/sqlt
Search
pinzolo
January 18, 2019
Technology
1
260
github.com/pinzolo/sqlt
pinzolo
January 18, 2019
Tweet
Share
More Decks by pinzolo
See All by pinzolo
rubygems-mfa.pdf
pinzolo
0
98
encoding/csv
pinzolo
0
700
Redmine 3.x
pinzolo
0
3.8k
Travis CI API LT
pinzolo
1
930
Gemfile.local
pinzolo
0
130
Tenderness driven development for Redmine plugin
pinzolo
1
4.8k
Other Decks in Technology
See All in Technology
コンテンツを支える 若手ゲームクリエイターの アートディレクションの事例紹介 / cagamefi-game
cyberagentdevelopers
PRO
1
130
GitHub Universe: Evaluating RAG apps in GitHub Actions
pamelafox
0
180
Oracle Base Database Service 技術詳細
oracle4engineer
PRO
5
49k
最速最小からはじめるデータプロダクト / Data Product MVP
amaotone
5
740
AWS re:Inventを徹底的に楽しむためのTips / Tips for thoroughly enjoying AWS re:Invent
yuj1osm
1
570
分布で見る効果検証入門 / ai-distributional-effect
cyberagentdevelopers
PRO
4
700
事業者間調整の行間を読む 調整の具体事例
sugiim
0
1.6k
Commitment vs Harrisonism - Keynote for Scrum Niseko 2024
miholovesq
6
1.1k
APIテスト自動化の勘所
yokawasa
7
4.2k
よくわからんサービスについての問い合わせが来たときの強い味方 Amazon Q について
kazzpapa3
0
220
サイバーエージェントにおける生成AIのリスキリング施策の取り組み / cyber-ai-reskilling
cyberagentdevelopers
PRO
2
200
LeSSに潜む「隠れWF病」とその処方箋
lycorptech_jp
PRO
2
120
Featured
See All Featured
Unsuck your backbone
ammeep
668
57k
Build The Right Thing And Hit Your Dates
maggiecrowley
32
2.4k
RailsConf & Balkan Ruby 2019: The Past, Present, and Future of Rails at GitHub
eileencodes
131
33k
Side Projects
sachag
452
42k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
167
49k
Optimising Largest Contentful Paint
csswizardry
33
2.9k
Speed Design
sergeychernyshev
24
570
Scaling GitHub
holman
458
140k
How GitHub (no longer) Works
holman
311
140k
Writing Fast Ruby
sferik
626
61k
Designing for humans not robots
tammielis
249
25k
The World Runs on Bad Software
bkeepers
PRO
65
11k
Transcript
github.com/ pinzolo/sqlt
$ whoami • NODA Masato • @pinzolo • Go ϓϥΠϕʔτͰ͍࢝Ίͯ2͙
Β͍ • SQLࣗͰॻ͖͍ͨ
sqltͱ text/template Λϕʔεʹͯ͠࡞ͨ͠ 2way SQL ϥΠϒϥ Ϧ
2way SQL ͬͯʁ • ΫΤϦϏϧμͷҰछ • ςϯϓϨʔτʹରͯ͠ύϥϝʔλΛͯ͠SQLΛੜ͢Δ • ςϯϓϨʔτࣗମ͕࣮ߦͰ͖ΔSQLͰ͋Δ
2way SQL ͬͯʁ • ΫΤϦϏϧμͷҰछ • ςϯϓϨʔτʹରͯ͠ύϥϝʔλΛͯ͠SQLΛੜ͢Δ • ςϯϓϨʔτࣗମ͕࣮ߦͰ͖ΔSQLͰ͋Δ <-
࠷େͷಛ
͜Μͳͭ ex: Doma SELECT * FROM item WHERE status =
'AVAILABLE' /*%if name != null*/ AND name = /*name*/'John Doe' /*%end*/
2way SQL ࣗମͷϝϦ οτɾσϝϦοτ ORM ૪͜͜ͰׂѪ
None
ͳͥ text/template ϕʔεͰ࡞Ζ͏ͱࢥͬͨ ͷ͔ʁ
ָ͔͔ͨͬͨ͠Β
text/template Λ͏ͱ if range ͱ੍͍ͬͨޚߏจ͕ఏڙ͞Ε͓ͯΓɺeq ne and or ͳͲͷ΄΅ if
ʹඞਢͳॲཧ࠷࣮ͷඞཁ͕ແ͍
text/template Λ͏ͱ template.FuncMap ܦ༝ͰςϯϓϨʔτͰ͑ΔؔΛొ Ͱ͖ΔͷͰɺSQLʹؔ͢ΔΈࠐΈؔϓϩδΣΫτຖͷศརؔ ͷొ͕༰қ
text/template Λ͏ͱ ύϑΥʔϚϯεΛຊՈʹͿΜ͛ΒΕΔɻ ʢͨͩ͠ɺવࣗͰScannerΛॻ͍ͨํ͕ΑΓߴੑೳͳͷʹ ͳΔʣ
ϝϦοτଟ͍
(ςϯϓϨʔτݴޠΛͬͯςϯϓϨʔτݴޠΛ࡞ΔΜ͔ͩΒ େ͕ྲྀ༻Ͱ͖ͯͨΓલͳͷ͚ͩͲ)
None
Sample code SQL SELECT * FROM users WHERE id IN
/*% in "ids" %*/(1, 2) AND name = /*% p "name" %*/'John Doe' /*%- if get "onlyMale" %*/ AND sex = 'MALE' /*%- end %*/ ORDER BY /*% out "order" %*/id
Sample code Go st := sqlt.New(sqlt.Postgres) query, args, err :=
st.Exec(s, map[string]interface{}{ "ids": []int{1, 2, 3}, "order": "name DESC", "onlyMale": false, "name": "Alex", })
Sample code Generated SQL SELECT * FROM users WHERE id
IN ($1, $2, $3) AND name = $4 ORDER BY name DESC
Sample code Generated SQL (ExecNamed) SELECT * FROM users WHERE
id IN (:ids__1, :ids__2, :ids__3) AND name = :name ORDER BY name DESC
Sample code Generated SQL (Annotation Option) SELECT * FROM users
WHERE id IN ($1, $2, $3)/*# ids */ AND name = $4/*# name */ ORDER BY name DESC
ͳͥ text/template Λ༻͍ͯ͜Μͳ͜ͱ͕ग़ དྷΔͷ͔ʁ
sqlt Λ࣮ݱ͍ͯ͠Δೋͭͷൃ 1.Template.Delims ʹΑΔ SQL ϑϨϯυϦʔͳσϦϛλ 2.ؔͰͳ͘ϝιουΛ FuncMap ʹొͯ͠෭࡞༻Λར༻͢ Δ
Template.Delims ຊདྷ {{ ͱ }} Ͱ͋Δ text/template ͷσϦϛλΛมߋ͢Δ ػೳɻ ͜ΕΛ
/*% ͱ %*/ ͱ͍͏SQLʹͱͬͯίϝϯτͰ͋Δจࣈʹ มߋ͍ͯ͠ΔͷͰɺςϯϓϨʔτͷ֤छ໋ྩΛແಟԽ͍ͯ͠Δɻ
ϝιουͷ෭࡞༻ར༻ FuncMap ʹొ͢ΔؔΛ७ਮͳؔͰͳ͘ɺಛఆͷΠϯελ ϯεʹॴଐ͢Δϝιουʹ͢Δ͜ͱʹΑͬͯεϨουϩʔΧϧͳ෭ ࡞༻Λར༻Ͱ͖Δɻ ͓ʹςϯϓϨʔτʹ͞Εͨύϥϝʔλ͔ΒɺSQL࣮ߦʹඞཁͳ ύϥϝʔλΛ࡞͢ΔͨΊʹར༻͍ͯ͠Δɻ
ྫ1ʣύϥϝʔλͷׂ SELECT * FROM users WHERE name LIKE /*% infix
"name" %*/'John Doe' /*% if get "available" %*/ AND status = /*% in "stats" %*/(NULL) /*% end %*/ /*% if get "email" %*/ AND email = /*% p "email" %*/'
[email protected]
' /*% end %*/
ྫ1ʣύϥϝʔλͷׂ 1.SQL࣮ߦ࣌ʹඞཁͳύϥϝʔλʢnameʣ 2.SQLߏங࣌ʹඞཁͳύϥϝʔλ(available) 3.྆ํʹඞཁͳύϥϝʔλʢemailʣ 4.ಛఆͷ݅࣌ʹඞཁͳύϥϝʔλʢstatsʣ ͜ͷ͏ͪɺ࣮ߦ࣌ʹ 2 ݅ʹΑͬͯ 4 Λഉআ͍ͨ͠
ྫ2ʣIN ۟ʹ͓͚ΔεϥΠεͷల։ ΄ͱΜͲͷ database/sql ͷυϥΠό WHERE id IN $1 ʹ
ରͯ͠εϥΠεΛͯ͠ల։͠ͳ͍ɻ
ྫ3ʣΤεέʔϓॲཧͨ͠ύϥϝʔλ͕ ඞཁ SELECT id , name , name = /*%
out "name" %*/'John' AS matched FROM users WHERE name LIKE /*% infix "name" %*/'John' ORDER BY matched DESC, name
ྫ3ʣΤεέʔϓॲཧͨ͠ύϥϝʔλ͕ ඞཁ ಉ͡ name ύϥϝʔλΛར༻͍ͯ͠Δ͕ɺ1ͭΊͦͷ··Ͱ͍ ͍ͷʹର͠ɺ2ͭΊ LIKE ͷରʹ͢ΔͨΊ %
_ ΛΤεέ ʔϓ͕ͨ͠ඞཁɻ
None
sqltͰղܾͰ͖Δ͜ͱ
1.SQLΛผϑΝΠϧͰཧͰ͖Δ 2.2way-SQLͳͷͰςετ͍͢͠ 3.Pure SQL ͳͷͰπʔϧͷԸܙΛड͚͍͢ 4.ύϥϝʔλʹ໊લΛ͚ΒΕΔ 5.in, suffix, prefix ͷΑ͏ͳ
SQL ઐ༻ؔʹΑΓແବͳ ॲཧΛආ͚ΒΕΔ 6.DBͷҧ͍ΛSQLͷத͚ͩʹด͡ࠐΊ͍͢
sqlt͕ղܾΕͳ͔ ͬͨ͜ͱ
SQLΠϯδΣΫγϣϯ
೦ͳ͕Β type Form struct { Name string } st :=
sqlt.New(sqlt.Postgres) query, args, err := st.Exec(s, map[string]interface{}{ "form": Form{Name: "' OR 1 = 1;"}, })
͜Μͳίʔυ͕ SELECT * FROM users /*%- $f := get "form"
%*/ WHERE name = '/*% $f.Name %*/'
͔͚ͯ͠·͏ SELECT * FROM users WHERE name = '' OR
1 = 1;
range Ͱ type V struct { Value string } st
:= sqlt.New(sqlt.Postgres) query, args, err := st.Exec(s, map[string]interface{}{ "values": []V{ V{"' OR 1 = 1;"}, V{"foo"}, V{"bar"}, }, })
ॻ͚ͯ͠·͏ SELECT * FROM users WHERE ( /*%- range $i,
$v := get "values" %*/ /*%- if ne $i 0 %*/ OR /*% end %*/ name = '/*% $v.Value %*/' /*%- end %*/ )
ͪͳΈʹ SELECT * FROM users WHERE ( /*%- range $i,
$v := get "values" %*/ /*%- if ne $i 0 %*/ OR /*% end %*/ name = /*% p (name "values" $i "Value") %*/'' /*%- end %*/ ) ҆શʹॻ͘ํ๏ఏڙ͍ͯ͠Δɻ
σϑΥϧτͷຒΊࠐΈॲཧʹରͯ͋͠·ΓʹແྗͳͷͰɺҰ࣌ม Λܦ༝͢Δ͚ͩͰ͍͘ΒͰSQLΠϯδΣΫγϣϯΛࠐΊͯ͠ ·͏ɻ σϑΥϧτͷຒΊࠐΈॲཧʹ hook Λॻ͚ͨΓ͢Δ͜ͱ͕ग़དྷΔ ͳΒରࡦͰ͖Δͷ͕ͩɺͦΜͳ͜ͱ͕ग़དྷΔͳΒ͓ͦΒ͘ html/template ͳΜͯଘࡏ͍ͯ͠ͳ͍ɻ
͑Δͷʁ • ϨϏϡʔ͕͔ͬ͠ΓճΔ • Θ͔͍ͬͯΔਓ͚ͩ • ݸਓϓϩμΫτ ʹ͑ͳ͍͜ͱͳ͍͔͠Εͳ͘ͳ͍ɻੵۃతʹ͓͢͢Ί ͠ͳ͍ɻ ʢ͕ࣗ͏ʹ͜ΕͰ·͍͍͔͋ͱࢥͬͯΔʣ
Կ͕͍͚ͳ͔ͬͨͷ͔ʁ • 7ʙ8ׂͷػೳΛຬͨ͢ͱ͍͏ϝϦοτΛ༏ઌ͗ͨ͢͠ • Ұ൪େࣄͳͱ͜ΖΛ֎͞ͳ͍ͱ͍͏ߟ͑Λ༏ઌ͖͢ • ָʹղܾͰ͖Δ͜ͱ୭͔͕ղܾ͍ͯ͠Δ • ͍͟ͱ͍͏ͱ͖ʹϨϕϧ෦ΛίϯτϩʔϧͰ͖Δ͔ͬͯͷ Ϛδେࣄ
ڭ܇
अಓʹ҆қʹඈͼ͔ͭͳ ͍