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
300
github.com/pinzolo/sqlt
pinzolo
January 18, 2019
Tweet
Share
More Decks by pinzolo
See All by pinzolo
rubygems-mfa.pdf
pinzolo
0
110
encoding/csv
pinzolo
0
750
Redmine 3.x
pinzolo
0
3.9k
Travis CI API LT
pinzolo
1
1k
Gemfile.local
pinzolo
0
140
Tenderness driven development for Redmine plugin
pinzolo
1
4.9k
Other Decks in Technology
See All in Technology
SEQUENCE object comparison - db tech showcase 2025 LT2
nori_shinoda
0
280
NewSQLや分散データベースを支えるRaftの仕組み - 仕組みを理解して知る得意不得意
hacomono
PRO
3
230
microCMSではじめるAIライティング
himaratsu
0
120
United™️ Airlines®️ Customer®️ USA Contact Numbers: Complete 2025 Support Guide
flyunitedguide
0
780
衛星運用をソフトウェアエンジニアに依頼したときにできあがるもの
sankichi92
1
230
モニタリング統一への道のり - 分散モニタリングツール統合のためのオブザーバビリティプロジェクト
niftycorp
PRO
1
360
推し書籍📚 / Books and a QA Engineer
ak1210
0
120
AIの全社活用を推進するための安全なレールを敷いた話
shoheimitani
2
640
united airlines ™®️ USA Contact Numbers: Complete 2025 Support Guide
flyunitedhelp
1
470
DBのスキルで生き残る技術 - AI時代におけるテーブル設計の勘所
soudai
PRO
55
22k
OpenTelemetryセマンティック規約の恩恵とMackerel APMにおける活用例 / SRE NEXT 2025
mackerelio
3
1.6k
オフィスビルを監視しよう:フィジカル×デジタルにまたがるSLI/SLO設計と運用の難しさ / Monitoring Office Buildings: The Challenge of Physical-Digital SLI/SLO Design & Operation
bitkey
1
350
Featured
See All Featured
What’s in a name? Adding method to the madness
productmarketing
PRO
23
3.5k
Performance Is Good for Brains [We Love Speed 2024]
tammyeverts
10
970
Building a Scalable Design System with Sketch
lauravandoore
462
33k
Docker and Python
trallard
45
3.5k
Fantastic passwords and where to find them - at NoRuKo
philnash
51
3.3k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
8
700
A Tale of Four Properties
chriscoyier
160
23k
Practical Orchestrator
shlominoach
189
11k
Mobile First: as difficult as doing things right
swwweet
223
9.7k
Designing Dashboards & Data Visualisations in Web Apps
destraynor
231
53k
Rebuilding a faster, lazier Slack
samanthasiow
83
9.1k
Visualizing Your Data: Incorporating Mongo into Loggly Infrastructure
mongodb
47
9.6k
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ׂͷػೳΛຬͨ͢ͱ͍͏ϝϦοτΛ༏ઌ͗ͨ͢͠ • Ұ൪େࣄͳͱ͜ΖΛ֎͞ͳ͍ͱ͍͏ߟ͑Λ༏ઌ͖͢ • ָʹղܾͰ͖Δ͜ͱ୭͔͕ղܾ͍ͯ͠Δ • ͍͟ͱ͍͏ͱ͖ʹϨϕϧ෦ΛίϯτϩʔϧͰ͖Δ͔ͬͯͷ Ϛδେࣄ
ڭ܇
अಓʹ҆қʹඈͼ͔ͭͳ ͍