Issue: raw sql execute quote bug
Author: fy0
State: closed
Created: 2017-07-07 11:59:05

Example:
```python
from playhouse.db_url import connect
db = connect('postgres://postgres@localhost:5432/')

print(db.quote_char)
# result is "

db.execute_sql('select %s from aaa', ('asd', ))
# Exception: LINE 1: select 'asd' from aaa

db.execute_sql('select "asd" from aaa')
# Exception: LINE 1: select "asd" from aaa

db.execute_sql('select \'asd\' from aaa')
# Exception: LINE 1: select 'asd' from aaa
```

Seems the quote_char is ', but " expected, right?
peewee version: from 2.9.3 to 2.10.1


It works fine for me:

```python
In [1]: from peewee import *

In [2]: db = PostgresqlDatabase('peewee_test')

In [3]: db.execute_sql('select %s', ('asd',)).fetchone()
Out[3]: ('asd',)
```

@coleifer  select is ok, but it's just a example. Table aaa is not exists so exception raised.
I just want to say quote seems wrong, and some statements were influenced.

More realistic example:
```python
# You know column or table name starts with digit is valid on PostgreSQL
# I guess that's a reason of db.quote_char design
# SQL: create table "123"("456" int);

db.execute_sql('select * from "123"')
# OK
db.execute_sql('select * from %s', ("123",))
# peewee.ProgrammingError: syntax error at or near "'123'"
# LINE 1: select * from '123'

db.execute_sql('insert into %s (%s) values (1)', ("123", "456"))
# peewee.ProgrammingError: syntax error at or near "'123'"
# LINE 1: insert into '123' ('456') values (1)
```

If design table by myself, I will never use these bad names.
Actually I'm trying to convert csv to postgreSQL table, so I should use origin names.


@fy0 -- Postgres quotes things using single quotes to express string literals. The quote_char is used to identify named entities, not for indicating string literals or doing some kind of escaping.

@coleifer  ' for string literals, " for identify named entities (columns, tables), right?  
Now table's name is 123, column's name is 456, and peewee's execute_sql crashed.  
I'm not sure I'm completely understand about how quote_char works, the crash is actually happened, others are my guess.  

some snippets from peewee:
```python
class QueryCompiler(object):
    def quote(self, s):
        return '%s%s%s' % (self.quote_char, s, self.quote_char)

class Database(object):
    compiler_class = QueryCompiler
    quote_char = '"'

    def compiler(self):
        return self.compiler_class(
            self.quote_char, self.interpolation, self.field_overrides,
            self.op_overrides)

    def execute(self, clause):
        return self.execute_sql(*self.compiler().parse_node(clause))

    def execute_sql(self, sql, params=None, require_commit=True):
        logger.debug((sql, params))
        with self.exception_wrapper:
            cursor = self.get_cursor()
            try:
                cursor.execute(sql, params or ())
            except Exception:
                if self.autorollback and self.get_autocommit():
                    self.rollback()
                raise
            else:
                if require_commit and self.get_autocommit():
                    self.commit()
        return cursor


class PostgresqlDatabase(Database):
    pass # inherited


class MySQLDatabase(Database):
    quote_char = '`'
```

emmmm, maybe it's doesn't matter about quote_char because compiler not work?

```python
db.execute(peewee.SQL('insert into %s (%s) values (1)', "123", "456"))
# peewee.ProgrammingError: syntax error at or near "'123'"
# LINE 1: insert into '123' ('456') values (1)
```

Something went wrong, but it's too hard to find and solve it to me.  
Fix it please. peewee is one of my favorite package of python, thanks for your hard works.
Going to bed, see you tomorrow.


I think you're mixing up quoting and escaping. They are two completely different things. In your last example (the INSERT INTO %s (%s) VALUES (1)) -- you are doing literally the exact opposite of what you should do. The table name and column name should be quoted literals -- not parameterized, while the actual value being inserted *should* be parameterized.

What you want would be:

    'INSERT INTO "123" ("456") VALUES (%s)', (1,)

As you can see, quote_char is a double quote, and it is used to wrap the named entities in the query -- the table and column. The "%s" is used to parameterize a query -- safely include values without risking SQL injection -- so we use the %s instead of the `1`.

@coleifer You mean there is no method to escape table and column names for raw sql execute .... %s is only for values, right?

Tested. Example of injection:
```python
class NewModel(peewee.Model):
    class Meta:
        database = db
        db_table = '1"23'

q = NewModel.select()
print(q)
# <class '__main__.NewModel'> SELECT "t1"."id" FROM "1"23" AS t1 []
```

```python
class NewModel(peewee.Model):
    class Meta:
        database = db
        db_table = 'asd" as t1; drop table "123" --'

q = NewModel.select()
print(q)
q.execute()

# <class '__main__.NewModel'> SELECT "t1"."id" FROM "asd" as t1; drop table "123" --" AS t1 []

# asd is another table
# now table '123' dropped
```

If admin interface provide a table design page, attackers can easily do everything they want by post evil names. I always thought names are escaped by default, It's so dangerous ...


@fy0 -- yes, `%s` is just for values, and double quotes are to wrap things like table or column names.

If you're using Peewee, you'll typically declare your table and columns as Python Model classes. I don't see any risk for SQL Injection unless you were doing something like prompting the user to supply you with the table and column names to execute queries on... which seems ridiculous.

As a dev, you control the table and col names. Shouldn't be an issue.

@coleifer Developers not always have 100% control. Such as model design tool, import data, peewee's pwiz and csv tools.

On the other hand, different database usually beaver difference with name quote sign. ORM's work is eliminate difference between databases. It's my advice.

Peewee *does* eliminate the differences in parameterizing queries...you don't have to think about the fact that sqlite uses "?" and mysql/pg use "%s". Peewee also prevents you from having to think about quoting, because peewee automatically quotes tables and columns for you using the db-specific character.

I'm not even sure what you're asking for at this point.

@coleifer I have the similar issue. 
```
class Base(playhouse.signals.Model):
    class Meta:
        database = db

    def partial_update(self, partial_data):
        SQL = "update %s set data = data || %s where tid = %s"
        vals = (self._meta.db_table, partial_data, self.tid)
        db_pointer.execute_sql(SQL, vals)

class Table1(Base):
    class Meta:
        db_table = 'table1'

class Table2(Base):
    class Meta:
        db_table = 'table2'
```
So partial_update function can be called on any of the Table. For that, I need to use %s and the respective table name to update.

The error I received is:
```
File "/home/stark/.local/lib/python2.7/site-packages/playhouse/postgres_ext.py", line 383, in execute_sql
    cursor.execute(sql, params or ())
ProgrammingError: syntax error at or near " ' table1 ' "
LINE 1: update 'table1' set data = data || '{"status": ...`
```

What should I do in this case?

PS: If I write the name of the table directly without using %s, then it works fine.

Why are you using an ORM like Peewee if you are writing simple update queries using plain sql?

```python

def partial_update(self, partial_data):
    model = type(self)    
    query = model.update({model.data: model.data.concat(partial_data)}).where(model.tid == self.tid)
    return query.execute()
```

As for mixing string interpolation and query parameterization, I strongly suggest that you don't do it that way...it's so clumsy. If you must, you should do this:

```python
query = "update %s set data = %%s" % table_name
db.execute_sql(query, params)
```

Also db_pointer?? This is python bro.

@coleifer Could not find how to update jsonb field without getting the data that's why used plain SQL.
I asked on StackOverflow but didn't get an answer. ([post)](https://stackoverflow.com/questions/48290053/how-to-update-jsonb-field-in-postgres-v9-4-using-peewee)

Running the update function you suggested, gave me the following error.
```
File "/home/stark/.local/lib/python2.7/site-packages/playhouse/postgres_ext.py", line 383, in execute_sql
    cursor.execute(sql, params or ())
DataError: invalid concatenation of jsonb objects
```
I have used `json.dumps(partial_data)` to send as string. Any suggestions.?

@pranjalsingi -- did you try not converting it to a string?

@coleifer It worked. I passed partial_data as a dictionary. 
Thank you, soo much.