Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MoSQL: More than SQL, but Less than ORM

MoSQL: More than SQL, but Less than ORM

** Please visit https://speakerdeck.com/mosky/mosql-more-than-sql-but-less-than-orm-at-pycon-apac-2013 for the newer slide. :)

It is the slides of the talk, "MoSQL: More than SQL, but Less than ORM", at PyCon TW 2013.

About MoSQL:

MoSQL is a Python library which lets you use common Python’s data structures to build SQLs, and provides a convenient model of result set.

http://mosql.mosky.tw/

Mosky Liu

May 25, 2013
Tweet

More Decks by Mosky Liu

Other Decks in Programming

Transcript

  1. 2 More than SQL, but Less than ORM More than

    SQL, but Less than ORM MoSQL MoSQL
  2. 3 Outline Outline • Why not SQL? Why not SQL?

    • Why ORM? Why ORM? • MoSQL MoSQL – SQL Builders SQL Builders – Model of Result Set Model of Result Set • Conclusion Conclusion
  3. 5 SQL Syntax SQL Syntax • SELECT * FROM article;

    SELECT * FROM article; • SELECT * FROM article LIMIT 1; SELECT * FROM article LIMIT 1; • add “ ORDER BY created ”? add “ ORDER BY created ”? • add “ OFFSET 10 ”? add “ OFFSET 10 ”? • add “ GROUP BY author ”? add “ GROUP BY author ”? • Is “ UPDATE article WHERE title='SQL' SET Is “ UPDATE article WHERE title='SQL' SET title='ORM' ” correct? title='ORM' ” correct?
  4. 7 SQL Injection SQL Injection • ') or '1'='1 ')

    or '1'='1 • ' or true; -- ' or true; -- • ' or 1=1; -- ' or 1=1; -- • ' or 2=2; -- ' or 2=2; -- • ' or 'str'='str'; -- ' or 'str'='str'; -- • … …
  5. 12 ORM Syntax ORM Syntax class User(Base): class User(Base): __tablename__

    = 'users' __tablename__ = 'users' name = Column(String) name = Column(String) fullname = Column(String) fullname = Column(String) password = Column(String) password = Column(String)
  6. 13 ORM Syntax (cont.) ORM Syntax (cont.) >>> fake_user =

    User('fakeuser', 'Invalid', >>> fake_user = User('fakeuser', 'Invalid', '12345') '12345') >>> session.add(fake_user) >>> session.add(fake_user) >>> for row in session.query(User, >>> for row in session.query(User, User.name).all(): User.name).all(): ... print row.User, row.name ... print row.User, row.name
  7. 15 SQL Injection SQL Injection • ' or true; --

    ' or true; -- • ' or 1=1; -- ' or 1=1; -- • ' or 1=1; # ' or 1=1; # • ' or 1=1; /* ' or 1=1; /* • ') or '1'='1 ') or '1'='1 • … … • Safer Safer
  8. 22 SQL Builders (cont.) SQL Builders (cont.) >>> from mosql.build

    import * >>> from mosql.build import * >>> >>> select('pycon') select('pycon') SELECT * FROM "pycon" SELECT * FROM "pycon" >>> select('pycon', >>> select('pycon', {'id': 'mosky'} {'id': 'mosky'}) ) SELECT * FROM "pycon" WHERE "id" = 'mosky' SELECT * FROM "pycon" WHERE "id" = 'mosky'
  9. 23 SQL Builders (cont.) SQL Builders (cont.) >>> insert('pycon', >>>

    insert('pycon', {'yr': 2013, 'id': 'masky'} {'yr': 2013, 'id': 'masky'}) ) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) INSERT INTO "pycon" ("id", "yr") VALUES ('masky', 2013) >>> update('pycon', >>> update('pycon', ... ... where={'id': 'masky'} where={'id': 'masky'}, , ... ... set ={'id': 'mosky'} set ={'id': 'mosky'} ... ) ... ) UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky' UPDATE "pycon" SET "id"='mosky' WHERE "id" = 'masky'
  10. 24 SQL Builders (cont.) SQL Builders (cont.) • insert(table, insert(table,

    set set, …) , …) • select(table, select(table, where where, …) , …) • update(table, update(table, where where, , set set, …) , …) • delete(table, delete(table, where where, …) , …) • ... ...
  11. 28 Model: Configure Connection Model: Configure Connection import psycopg2.pool import

    psycopg2.pool from mosql.result import Model from mosql.result import Model pool = psycopg2.pool.SimpleConnectionPool(1, 5, pool = psycopg2.pool.SimpleConnectionPool(1, 5, database='mosky') database='mosky') class PostgreSQL(Model): class PostgreSQL(Model): getconn = pool.getconn getconn = pool.getconn putconn = pool.putconn putconn = pool.putconn
  12. 29 Model: Set the Name of Table Model: Set the

    Name of Table class Person(PostgreSQL): class Person(PostgreSQL): table = 'person' table = 'person' >>> Person.select( >>> Person.select({'person_id': 'mosky'} {'person_id': 'mosky'}) ) {'name': ['Mosky Liu'], 'person_id': ['mosky']} {'name': ['Mosky Liu'], 'person_id': ['mosky']} >>> Person.where(person_id= >>> Person.where(person_id=('andy', 'mosky') ('andy', 'mosky')) ) {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': {'name': ['Andy Warhol', 'Mosky Liu'], 'person_id': ['andy', 'mosky']} ['andy', 'mosky']}
  13. 30 Model: Make Queries Model: Make Queries Person. Person.select select({'person_id':

    'mosky'}) ({'person_id': 'mosky'}) Person. Person.insert insert({'person_id': 'tina'}) ({'person_id': 'tina'}) Person. Person.update update( ( where={'person_id': 'mosky'}, where={'person_id': 'mosky'}, set ={'name' : 'Yiyu Liu'} set ={'name' : 'Yiyu Liu'} ) ) Person. Person.delete delete({'person_id': 'tina'}) ({'person_id': 'tina'})
  14. 31 Model: Squash Columns Model: Squash Columns class Person(PostgreSQL): class

    Person(PostgreSQL): table = 'person' table = 'person' squashed = set(['person_id', 'name']) squashed = set(['person_id', 'name']) >>> Person.select({'person_id': 'mosky'}) >>> Person.select({'person_id': 'mosky'}) {'name': {'name': 'Mosky Liu' 'Mosky Liu', 'person_id': , 'person_id': 'mosky' 'mosky'} } >>> Person.where(person_id=('andy', 'mosky')) >>> Person.where(person_id=('andy', 'mosky')) {'name': {'name': 'Andy Warhol' 'Andy Warhol', 'person_id': , 'person_id': 'andy' 'andy'} }
  15. 32 Model: Arrange Model: Arrange class Person(PostgreSQL): class Person(PostgreSQL): ...

    ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person.arrange( >>> for person in Person.arrange({'person_id': {'person_id': ('andy', 'mosky')} ('andy', 'mosky')}): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}
  16. 33 Model: Arrange (cont.) Model: Arrange (cont.) >>> for detail

    in >>> for detail in Detail Detail.arrange({'person_id': .arrange({'person_id': ('mosky', 'andy')}): ('mosky', 'andy')}): ... print detail ... print detail ... ... {'detail_id': [5], {'detail_id': [5], 'key': 'email', 'key': 'email', 'person_id': 'andy', 'person_id': 'andy', 'val': ['[email protected]']} 'val': ['[email protected]']} ... ...
  17. 34 Model: Find Model: Find class Person(PostgreSQL): class Person(PostgreSQL): ...

    ... arrange_by = ('person_id', ) arrange_by = ('person_id', ) >>> for person in Person. >>> for person in Person.find find( (person_id=('andy', person_id=('andy', 'mosky') 'mosky')): ): ... print person ... print person {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Andy Warhol', 'person_id': 'andy'} {'name': 'Mosky Liu', 'person_id': 'mosky'} {'name': 'Mosky Liu', 'person_id': 'mosky'}
  18. 35 Model: Identify a Row Model: Identify a Row class

    Person(PostgreSQL): class Person(PostgreSQL): ... ... ident_by = ('person_id', ) ident_by = ('person_id', )
  19. 36 Model: Modification Model: Modification >>> p = Person.where(person_id='mosky') >>>

    p = Person.where(person_id='mosky') >>> >>> p['name'] = 'Yiyu Liu' p['name'] = 'Yiyu Liu' >>> >>> p.name = 'Yiyu Liu' p.name = 'Yiyu Liu' >>> p.save() >>> p.save() >>> d = >>> d = Detail Detail.where( .where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p['val'][0] = '<modified email>' p['val'][0] = '<modified email>' >>> >>> p.val[0] = '<modified email>' p.val[0] = '<modified email>' >>> p.save() >>> p.save()
  20. 37 Model: Pop and Append Model: Pop and Append >>>

    d = Detail.where( >>> d = Detail.where(person_id='mosky', key='email' person_id='mosky', key='email') ) >>> >>> p.pop(-1) p.pop(-1) >>> >>> p.append({'val': '<new mail>'}) p.append({'val': '<new mail>'}) >>> p.save() >>> p.save()
  21. 38 Model: Default Clauses Model: Default Clauses class Person(PostgreSQL): class

    Person(PostgreSQL): ... ... clauses = dict( clauses = dict( order_by=('person_id', ) order_by=('person_id', ) ) )
  22. 39 Performance Performance • About About 4x 4x faster than

    SQLAlchemy. faster than SQLAlchemy. • Just a little bit slower than pure SQL. Just a little bit slower than pure SQL.
  23. 40 Security Security • Security by default. Security by default.

    • Use escaping technique. Use escaping technique. • Prevent SQL injection from both value Prevent SQL injection from both value and identifier. and identifier. • Passed the tests from Passed the tests from sqlmap sqlmap at level=5 at level=5 and risk=3. and risk=3.
  24. 41 Conclusion Conclusion • Easy-to-Learn Easy-to-Learn • Convenient Convenient •

    Faster Faster • Secure Secure • sudo pip install mosql sudo pip install mosql • http://mosql.mosky.tw/ http://mosql.mosky.tw/ • Welcome to fork! Welcome to fork!