Repository: kayak/pypika (python)

Issue:
Add support for parametrized queries
Hello,

Props for this wonderful tool!

My understanding is that the end product for the user is always the SQL string as returned by calling `str` on a Query object. Such string can then be passed to whatever database interface the user is using. However, SQL interfaces also accept so-called parametrized queries, where the input isn't a single SQL string, but rather a string containing placeholders alongside a tuple containing values to replace the placeholders with. Parameterized queries are very important as they're the only working solution against SQL injection.

The interface could be improved such that the Query object would have two properties `statement` and `values` corresponding respectively to the parametrized statement and the values for this statement. The behavior of `str` would still be relevant as it's always useful to know what a query looks like even when parametrized, although its output should be documented as insecure with unstrusted data.

Pull request description:
Basic parametrised query generation (WIP)
This PR adds parametrized query building by adding a `Parameter(':1')` term that will be the parameter placeholder.
Due to placeholders in SQL for parametrized queries being so varied (even between drivers or setup for the same dialect) that I decided to leave implementation as explicit as possible.
Whatever is indicated as the string is included verbatim into the SQL.

An example of this madness:
PostgreSQL → `$number` OR `%s` + `:name` (depending on driver)
MySQL → `%s`
SQLite → `?`
Vertica → `:name`
Oracle → `:number` + `:name`
MSSQL → `%(name)s` OR `:name` + `:number` (depending on driver)

This PR is a work-in-progress:
* [x] Added Python3.7 and PyPy3.5 to Travis builds.
* [x] Added some tests of when to NOT parametrize.
* [x] Added some parameter tests.
* [x] Implemented a `Parameter()` term, and added to root `__init__.py` for convenience
* [x] Removed `__str__()` where the parent has the same variant implemented.
* [x] Update documentation

Fixes #113, potential workaround for some of #3 

Patch:
diff --git a/.travis.yml b/.travis.yml
index b17cb8c..76fee8f 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -7,6 +7,13 @@ python:
     - "3.5"
     - "3.6"
 
+matrix:
+  include:
+  - python: "3.7"
+    dist: xenial
+  - python: "pypy3.5"
+    dist: xenial
+
 install:
     - "python setup.py install"
     - "pip install coverage"
diff --git a/README.rst b/README.rst
index ae46faa..dafe99c 100644
--- a/README.rst
+++ b/README.rst
@@ -728,6 +728,44 @@ The syntax for joining tables is the same as when selecting data
    JOIN "profiles" ON "profiles"."customer_id"="customers"."id"
    SET "customers"."lname"="profiles"."lname"
 
+Parametrized Queries
+^^^^^^^^^^^^^^^^^^^^
+
+PyPika allows you to use ``Parameter(str)`` term as a placeholder for parametrized queries.
+
+.. code-block:: python
+
+    customers = Table('customers')
+
+    q = Query.into(customers).columns('id', 'fname', 'lname')
+        .insert(Parameter(':1'), Parameter(':2'), Parameter(':3'))
+
+.. code-block:: sql
+
+    INSERT INTO customers (id,fname,lname) VALUES (:1,:2,:3)
+
+This allows you to build prepared statements, and/or avoid SQL-injection related risks.
+
+Due to the mix of syntax for parameters, depending on connector/driver, it is required that you specify the parameter token explicitly.
+
+An example of some common SQL parameter styles used in Python drivers are:
+
+PostgreSQL:
+    ``$number`` OR ``%s`` + ``:name`` (depending on driver)
+MySQL:
+    ``%s``
+SQLite:
+    ``?``
+Vertica:
+    ``:name``
+Oracle:
+    ``:number`` + ``:name``
+MSSQL:
+    ``%(name)s`` OR ``:name`` + ``:number`` (depending on driver)
+
+You can find out what parameter style is needed for DBAPI compliant drivers here: https://www.python.org/dev/peps/pep-0249/#paramstyle or in the DB driver documentation.
+
+
 .. _tutorial_end:
 
 
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 2dccc71..cacd0a4 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -66,6 +66,7 @@ from .terms import (
     Not,
     Rollup,
     Tuple,
+    Parameter,
 )
 # noinspection PyUnresolvedReferences
 from .utils import (
diff --git a/pypika/terms.py b/pypika/terms.py
index ccedefb..8d4f8db 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -221,6 +221,20 @@ class Term(object):
         raise NotImplementedError()
 
 
+class Parameter(Term):
+    is_aggregate = None
+
+    def __init__(self, placeholder):
+        super(Parameter, self).__init__()
+        self.placeholder = placeholder
+
+    def fields(self):
+        return []
+
+    def get_sql(self, **kwargs):
+        return str(self.placeholder)
+
+
 class Negative(Term):
     def __init__(self, term):
         super(Negative, self).__init__()
@@ -241,10 +255,6 @@ class ValueWrapper(Term):
         return []
 
     def get_sql(self, quote_char=None, **kwargs):
-        sql = self._get_value_sql(quote_char=quote_char, **kwargs)
-        return alias_sql(sql, self.alias, quote_char)
-
-    def _get_value_sql(self, quote_char=None, **kwargs):
         # FIXME escape values
         if isinstance(self.value, Term):
             return self.value.get_sql(quote_char=quote_char, **kwargs)
@@ -395,9 +405,6 @@ class Tuple(Criterion):
         self.values = [self.wrap_constant(value)
                        for value in values]
 
-    def __str__(self):
-        return self.get_sql()
-
     def fields(self):
         return list(itertools.chain(*[value.fields()
                                       for value in self.values]))
@@ -738,9 +745,6 @@ class Not(Criterion):
                                                          **kwargs))
         return alias_sql(sql, self.alias, quote_char=quote_char)
 
-    def __str__(self):
-        return self.get_sql(quote_char='"')
-
     @ignore_copy
     def __getattr__(self, name):
         """
diff --git a/tox.ini b/tox.ini
index 2b989c4..d4e9ffa 100644
--- a/tox.ini
+++ b/tox.ini
@@ -1,5 +1,5 @@
 [tox]
-envlist = py27,py33,py34,py35,py36
+envlist = py27,py33,py34,py35,py36,py37,pypy3
 [testenv]
-deps = aenum
-commands = setup.py build test
\ No newline at end of file
+deps = -r requirements.txt
+commands = python setup.py build test


Test patch:
diff --git a/pypika/tests/test_parameter.py b/pypika/tests/test_parameter.py
new file mode 100644
index 0000000..07453e3
--- /dev/null
+++ b/pypika/tests/test_parameter.py
@@ -0,0 +1,47 @@
+import unittest
+
+from pypika import (
+    Tables,
+    Query,
+    Parameter
+)
+
+
+class ParametrizedTests(unittest.TestCase):
+    table_abc, table_efg = Tables('abc', 'efg')
+
+    def test_param_insert(self):
+        q = Query.into(self.table_abc).columns('a', 'b', 'c').insert(Parameter('?'), Parameter('?'), Parameter('?'))
+
+        self.assertEqual('INSERT INTO "abc" ("a","b","c") VALUES (?,?,?)', q.get_sql())
+
+    def test_param_select_join(self):
+        q = Query.from_(self.table_abc).select('*').where(self.table_abc.category == Parameter('%s'))\
+            .join(self.table_efg).on(self.table_abc.id == self.table_efg.abc_id)\
+            .where(self.table_efg.date >= Parameter('%s')).limit(10)
+
+        self.assertEqual(
+            'SELECT * FROM "abc" JOIN "efg" ON "abc"."id"="efg"."abc_id" WHERE "abc"."category"=%s AND "efg"."date">=%s LIMIT 10',
+            q.get_sql())
+
+    def test_param_select_subquery(self):
+        q = Query.from_(self.table_abc).select('*').where(self.table_abc.category == Parameter('&1'))\
+            .where(self.table_abc.id.isin(
+                Query.from_(self.table_efg).select(self.table_efg.abc_id).where(self.table_efg.date >= Parameter('&2'))
+            )).limit(10)
+
+        self.assertEqual(
+            'SELECT * FROM "abc" WHERE "category"=&1 AND "id" IN (SELECT "abc_id" FROM "efg" WHERE "date">=&2) LIMIT 10',
+            q.get_sql())
+
+    def test_join(self):
+        subquery = Query.from_(self.table_efg).select(self.table_efg.fiz, self.table_efg.buz).where(self.table_efg.buz == Parameter(':buz'))
+
+        q = Query.from_(self.table_abc).join(subquery).on(
+            self.table_abc.bar == subquery.buz
+        ).select(self.table_abc.foo, subquery.fiz).where(self.table_abc.bar == Parameter(':bar'))
+
+        self.assertEqual(
+            'SELECT "abc"."foo","sq0"."fiz" FROM "abc" JOIN (SELECT "fiz","buz" FROM "efg" WHERE "buz"=:buz)'
+            ' "sq0" ON "abc"."bar"="sq0"."buz" WHERE "abc"."bar"=:bar',
+            q.get_sql())
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 1f3a14f..325f8f0 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -81,6 +81,11 @@ class SelectTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo" FROM "abc"', str(q))
 
+    def test_select__column__single__alias__str(self):
+        q = Query.from_(self.table_abc).select(self.table_abc.foo.as_('bar'))
+
+        self.assertEqual('SELECT "foo" "bar" FROM "abc"', str(q))
+
     def test_select__column__single__field(self):
         t = Table('abc')
         q = Query.from_(t).select(t.foo)
diff --git a/pypika/tests/test_updates.py b/pypika/tests/test_updates.py
index bc0a88c..a6d731c 100644
--- a/pypika/tests/test_updates.py
+++ b/pypika/tests/test_updates.py
@@ -9,6 +9,7 @@ __email__ = "theys@kayak.com"
 
 class UpdateTests(unittest.TestCase):
     table_abc = Table('abc')
+    table_def = Table('def')
 
     def test_empty_query(self):
         q = Query.update('abc')
@@ -35,6 +36,10 @@ class UpdateTests(unittest.TestCase):
         q = Query.update('abc').set('foo', None)
         self.assertEqual('UPDATE "abc" SET "foo"=null', str(q))
 
+    def test_update_with_join(self):
+        q = Query.update(self.table_abc).join(self.table_def).on(self.table_def.abc_id == self.table_abc.id).set(self.table_abc.lname, self.table_def.lname)
+        self.assertEqual('UPDATE "abc" JOIN "def" ON "def"."abc_id"="abc"."id" SET "abc"."lname"="def"."lname"', str(q))
+
 
 class PostgresUpdateTests(unittest.TestCase):
     table_abc = Table('abc')