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
88
encoding/csv
pinzolo
0
620
Redmine 3.x
pinzolo
0
3.8k
Travis CI API LT
pinzolo
1
850
Gemfile.local
pinzolo
0
120
Tenderness driven development for Redmine plugin
pinzolo
1
4.7k
Other Decks in Technology
See All in Technology
Kernel MemoryでAzure OpenAI Serviceとお手軽データソース連携
mitsuzono
1
260
GraphQL 成熟度モデルの紹介と、プロダクトに当てはめた事例 / GraphQL maturity model
mh4gf
7
1.3k
チームでロジカルシンキングに改めて向き合っている話 〜学習環境と実践⽅法〜
sansantech
PRO
3
2.6k
今年のRubyKaigiはProfiler Year🤘
osyoyu
0
170
ChatworkのSRE部って実は 半分くらいPlatform Engineering部かもしれない
saramune
0
160
アクセシビリティを考慮したUI/CSSフレームワーク・ライブラリ選定
yajihum
2
1k
よく聞くけど使ったことないソフトウェアNo.1 KafkaとSnowflake
foursue
4
360
Cloud Native Java with Spring Boot (CNCF Aarhus, April 2024)
thomasvitale
1
170
20240418_Google ColabにLLMが搭載されたようなのでPython x データ分析の勉強方法を考えてみる
doradora09
0
140
アクセス制御にまつわる改善 / Improving access control
itkq
0
550
一生覚えておきたい「システム開発=コミュニケーション」〜初めての実務案件振り返りLT〜
maimyyym
1
160
複雑な構成要素を持つUIとの向き合い方 〜新・支出グラフでの実例〜 / B43 TECH TALK
nakamuuu
0
140
Featured
See All Featured
Rails Girls Zürich Keynote
gr2m
91
13k
Clear Off the Table
cherdarchuk
84
310k
The World Runs on Bad Software
bkeepers
PRO
61
6.7k
Principles of Awesome APIs and How to Build Them.
keavy
121
16k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
60
14k
5 minutes of I Can Smell Your CMS
philhawksworth
199
19k
Infographics Made Easy
chrislema
238
18k
A designer walks into a library…
pauljervisheath
200
23k
Faster Mobile Websites
deanohume
299
30k
Testing 201, or: Great Expectations
jmmastey
28
6.4k
Visualization
eitanlees
136
14k
In The Pink: A Labor of Love
frogandcode
138
21k
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ׂͷػೳΛຬͨ͢ͱ͍͏ϝϦοτΛ༏ઌ͗ͨ͢͠ • Ұ൪େࣄͳͱ͜ΖΛ֎͞ͳ͍ͱ͍͏ߟ͑Λ༏ઌ͖͢ • ָʹղܾͰ͖Δ͜ͱ୭͔͕ղܾ͍ͯ͠Δ • ͍͟ͱ͍͏ͱ͖ʹϨϕϧ෦ΛίϯτϩʔϧͰ͖Δ͔ͬͯͷ Ϛδେࣄ
ڭ܇
अಓʹ҆қʹඈͼ͔ͭͳ ͍