Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
5
Slide 14
Slide 14 text
Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
The Usage, Performance, and Security
5
Slide 15
Slide 15 text
Outline
Why not SQL? But ...
Why ORM? But ...
MoSQL
The Usage, Performance, and Security
Demo
5
Slide 16
Slide 16 text
Doc:
http://mosql.mosky.tw
Slide 17
Slide 17 text
Why not SQL?
Slide 18
Slide 18 text
Hard to Use
8
Slide 19
Slide 19 text
Hard to Use
SELECT * FROM article LIMIT 1;
8
Slide 20
Slide 20 text
Hard to Use
SELECT * FROM article LIMIT 1;
add ORDER BY created?
8
Slide 21
Slide 21 text
Hard to Use
SELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
8
Slide 22
Slide 22 text
Hard to Use
SELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
add GROUP BY author?
8
Slide 23
Slide 23 text
Hard to Use
SELECT * FROM article LIMIT 1;
add ORDER BY created?
add OFFSET 10?
add GROUP BY author?
UPDATE article WHERE title='SQL'
SET title='ORM'?
8
Slide 24
Slide 24 text
Hard to Use
9
Slide 25
Slide 25 text
Hard to Use
Programming Error
9
Slide 26
Slide 26 text
Hard to Use
Programming Error
Programming Error
9
Slide 27
Slide 27 text
Hard to Use
Programming Error
Programming Error
Programming Error
9
Slide 28
Slide 28 text
Hard to Use
Programming Error
Programming Error
Programming Error
!@#$
9
Slide 29
Slide 29 text
May Be Injected
10
Slide 30
Slide 30 text
May Be Injected
'WHERE ' + ' AND '.join(
"%s = '%s'" for k, v in inputs
)
10
Slide 31
Slide 31 text
May Be Injected
'WHERE ' + ' AND '.join(
"%s = '%s'" for k, v in inputs
)
Cracker can inject from value
10
Slide 32
Slide 32 text
May Be Injected
'WHERE ' + ' AND '.join(
"%s = '%s'" for k, v in inputs
)
Cracker can inject from value
or identifier, actually.
10
Slide 33
Slide 33 text
May Be Injected
'WHERE ' + ' AND '.join(
"%s = '%s'" for k, v in inputs
)
Cracker can inject from value
or identifier, actually.
DON'T copy the code here!
10
Slide 34
Slide 34 text
It seems bad! But ...
Slide 35
Slide 35 text
SQL ...
12
Slide 36
Slide 36 text
SQL ...
is fastest way to communicate with db,
12
Slide 37
Slide 37 text
SQL ...
is fastest way to communicate with db,
and everyone understands or learned it.
12
Slide 38
Slide 38 text
Why ORM?
Slide 39
Slide 39 text
Easy to Use
14
Slide 40
Slide 40 text
Easy to Use
class Person(Base):
__tablename__ = 'person'
person_id = Column(String, primary_key=True)
name = Column(String)
...
14
Slide 41
Slide 41 text
Easy to Use
15
Slide 42
Slide 42 text
Easy to Use
mosky = Person('mosky', 'Mosky Liu')
session.add(mosky)
15
Slide 43
Slide 43 text
Easy to Use
mosky = Person('mosky', 'Mosky Liu')
session.add(mosky)
for person in session.query(Person).all():
print person.name, person.person_id
15
Slide 44
Slide 44 text
Easy to Use
mosky = Person('mosky', 'Mosky Liu')
session.add(mosky)
for person in session.query(Person).all():
print person.name, person.person_id
Let you forget the ugly SQL so far.
15
Slide 45
Slide 45 text
SQL Injection Free
16
Slide 46
Slide 46 text
SQL Injection Free
Usually ORM guarantees it.
16
Slide 47
Slide 47 text
It seems good! But ...
Slide 48
Slide 48 text
ORM ...
18
Slide 49
Slide 49 text
ORM ...
is slower,
18
Slide 50
Slide 50 text
ORM ...
is slower,
and you need to learn it from scratch.
18
Slide 51
Slide 51 text
ORM ...
is slower,
and you need to learn it from scratch.
Sometimes it is just a black box.
18
Slide 52
Slide 52 text
SQL vs. ORM
SQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
Slide 53
Slide 53 text
So ... MoSQL
Slide 54
Slide 54 text
The First Glance
21
Slide 55
Slide 55 text
The First Glance
from mosql.query import select
print select('person')
21
Slide 56
Slide 56 text
The First Glance
from mosql.query import select
print select('person')
-> SELECT * FROM "person"
21
Slide 57
Slide 57 text
Map is just condition
22
Slide 58
Slide 58 text
Map is just condition
select('person', {
'person_id': 'mosky'
})
22
Slide 59
Slide 59 text
Map is just condition
select('person', {
'person_id': 'mosky'
})
-> SELECT * FROM "person"
WHERE "person_id" = 'mosky'
22
Slide 60
Slide 60 text
Sequence is just a list
23
Slide 61
Slide 61 text
Sequence is just a list
select('person',
select=('name', )
)
23
Slide 62
Slide 62 text
Sequence is just a list
select('person',
select=('name', )
)
-> SELECT "name" FROM "person"
23
Slide 63
Slide 63 text
Map is also a set-list
24
Slide 64
Slide 64 text
Map is also a set-list
insert('person', {
'person_id': 'mosky',
'name' : 'Mosky Liu'
})
24
Slide 65
Slide 65 text
Map is also a set-list
insert('person', {
'person_id': 'mosky',
'name' : 'Mosky Liu'
})
-> INSERT INTO
"person" ("person_id", "name")
VALUES ('mosky', 'Mosky Liu')
24
Slide 66
Slide 66 text
Order doesn't matter
25
Slide 67
Slide 67 text
Order doesn't matter
update('person',
where={'person_id': 'mosky'},
set ={'name' : 'Mosky Liu'},
})
25
Slide 68
Slide 68 text
Order doesn't matter
update('person',
where={'person_id': 'mosky'},
set ={'name' : 'Mosky Liu'},
})
-> UPDATE "person"
SET "name" = 'Mosky Liu'
WHERE "person_id" = 'mosky'
25
Slide 69
Slide 69 text
Operator also works!
26
Slide 70
Slide 70 text
Operator also works!
select('person', {
'age >=': 20
})
26
Slide 71
Slide 71 text
Operator also works!
select('person', {
'age >=': 20
})
-> SELECT * FROM "person"
WHERE "age" >= 20
26
Slide 72
Slide 72 text
All from
the native data structures!
Slide 73
Slide 73 text
The Overview
28
Slide 74
Slide 74 text
The Overview
insert(table, set, ...)
28
Slide 75
Slide 75 text
The Overview
insert(table, set, ...)
select(table, where, ...)
28
Join is also available
select(
'person',
{'person_id': 'mosky'},
joins=left_join('detail',using=('person_id',))
)
31
Slide 83
Slide 83 text
Join is also available
select(
'person',
{'person_id': 'mosky'},
joins=left_join('detail',using=('person_id',))
)
-> SELECT * FROM "person" LEFT JOIN "detail" USING
("person_id") WHERE "person_id" = 'mosky'
31
A Partial Query
fixed_args = {'table': 'person'}
person_select = select.breed(fixed_args)
person_select()
-> SELECT * FROM "person"
32
Slide 87
Slide 87 text
A Partial Query
fixed_args = {'table': 'person'}
person_select = select.breed(fixed_args)
person_select()
-> SELECT * FROM "person"
select('person')
32
Slide 88
Slide 88 text
A Partial Query
fixed_args = {'table': 'person'}
person_select = select.breed(fixed_args)
person_select()
-> SELECT * FROM "person"
select('person')
-> SELECT * FROM "person"
32
Slide 89
Slide 89 text
Performance
33
Slide 90
Slide 90 text
Performance
About 4x faster than SQLAlchemy.
33
Slide 91
Slide 91 text
Performance
About 4x faster than SQLAlchemy.
Just a little bit slower than pure SQL.
33
Slide 92
Slide 92 text
Security
34
Slide 93
Slide 93 text
Security
Security by default.
34
Slide 94
Slide 94 text
Security
Security by default.
Use escaping technique.
34
Slide 95
Slide 95 text
Security
Security by default.
Use escaping technique.
Prevent SQL injection from both value and
identifier.
34
Slide 96
Slide 96 text
Security
Security by default.
Use escaping technique.
Prevent SQL injection from both value and
identifier.
Passed the tests from sqlmap at level=5 and
risk=3.
34
Slide 97
Slide 97 text
SQL vs. ORM
SQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
Slide 98
Slide 98 text
SQL < ______ < ORM
SQL ORM
Easy-to-Use V
Secure V
Easy-to-Learn V
Fast V
Slide 99
Slide 99 text
SQL < MoSQL < ORM
SQL MoSQL ORM
Easy-to-Use V V
Secure V V
Easy-to-Learn V V
Fast V V
Slide 100
Slide 100 text
Demo
Slide 101
Slide 101 text
Demo
39
Slide 102
Slide 102 text
Demo
Arbitrary Query with Web
39
Slide 103
Slide 103 text
Demo
Arbitrary Query with Web
Serious Usage using Class
39
Slide 104
Slide 104 text
Demo
Arbitrary Query with Web
Serious Usage using Class
All the code are in the Github!
39
Slide 105
Slide 105 text
The End
Slide 106
Slide 106 text
The End
41
Slide 107
Slide 107 text
The End
MoSQL is ...
41
Slide 108
Slide 108 text
The End
MoSQL is ...
Easy-to-Use
41
Slide 109
Slide 109 text
The End
MoSQL is ...
Easy-to-Use
Easy-to-Learn
41
Slide 110
Slide 110 text
The End
MoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
41
Slide 111
Slide 111 text
The End
MoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
41
Slide 112
Slide 112 text
The End
MoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
sudo pip install mosql
41
Slide 113
Slide 113 text
The End
MoSQL is ...
Easy-to-Use
Easy-to-Learn
Secure
Fast
sudo pip install mosql
http://mosql.mosky.tw/
41