Home | History | Annotate | Download | only in _sqlite
      1 /* statement.c - the statement type
      2  *
      3  * Copyright (C) 2005-2010 Gerhard Hring <gh (at) ghaering.de>
      4  *
      5  * This file is part of pysqlite.
      6  *
      7  * This software is provided 'as-is', without any express or implied
      8  * warranty.  In no event will the authors be held liable for any damages
      9  * arising from the use of this software.
     10  *
     11  * Permission is granted to anyone to use this software for any purpose,
     12  * including commercial applications, and to alter it and redistribute it
     13  * freely, subject to the following restrictions:
     14  *
     15  * 1. The origin of this software must not be misrepresented; you must not
     16  *    claim that you wrote the original software. If you use this software
     17  *    in a product, an acknowledgment in the product documentation would be
     18  *    appreciated but is not required.
     19  * 2. Altered source versions must be plainly marked as such, and must not be
     20  *    misrepresented as being the original software.
     21  * 3. This notice may not be removed or altered from any source distribution.
     22  */
     23 
     24 #include "statement.h"
     25 #include "cursor.h"
     26 #include "connection.h"
     27 #include "microprotocols.h"
     28 #include "prepare_protocol.h"
     29 #include "sqlitecompat.h"
     30 
     31 /* prototypes */
     32 static int pysqlite_check_remaining_sql(const char* tail);
     33 
     34 typedef enum {
     35     LINECOMMENT_1,
     36     IN_LINECOMMENT,
     37     COMMENTSTART_1,
     38     IN_COMMENT,
     39     COMMENTEND_1,
     40     NORMAL
     41 } parse_remaining_sql_state;
     42 
     43 typedef enum {
     44     TYPE_INT,
     45     TYPE_LONG,
     46     TYPE_FLOAT,
     47     TYPE_STRING,
     48     TYPE_UNICODE,
     49     TYPE_BUFFER,
     50     TYPE_UNKNOWN
     51 } parameter_type;
     52 
     53 int pysqlite_statement_create(pysqlite_Statement* self, pysqlite_Connection* connection, PyObject* sql)
     54 {
     55     const char* tail;
     56     int rc;
     57     PyObject* sql_str;
     58     char* sql_cstr;
     59 
     60     self->st = NULL;
     61     self->in_use = 0;
     62 
     63     if (PyString_Check(sql)) {
     64         sql_str = sql;
     65         Py_INCREF(sql_str);
     66     } else if (PyUnicode_Check(sql)) {
     67         sql_str = PyUnicode_AsUTF8String(sql);
     68         if (!sql_str) {
     69             rc = PYSQLITE_SQL_WRONG_TYPE;
     70             return rc;
     71         }
     72     } else {
     73         rc = PYSQLITE_SQL_WRONG_TYPE;
     74         return rc;
     75     }
     76 
     77     self->in_weakreflist = NULL;
     78     self->sql = sql_str;
     79 
     80     sql_cstr = PyString_AsString(sql_str);
     81 
     82     Py_BEGIN_ALLOW_THREADS
     83     rc = sqlite3_prepare(connection->db,
     84                          sql_cstr,
     85                          -1,
     86                          &self->st,
     87                          &tail);
     88     Py_END_ALLOW_THREADS
     89 
     90     self->db = connection->db;
     91 
     92     if (rc == SQLITE_OK && pysqlite_check_remaining_sql(tail)) {
     93         (void)sqlite3_finalize(self->st);
     94         self->st = NULL;
     95         rc = PYSQLITE_TOO_MUCH_SQL;
     96     }
     97 
     98     return rc;
     99 }
    100 
    101 int pysqlite_statement_bind_parameter(pysqlite_Statement* self, int pos, PyObject* parameter, int allow_8bit_chars)
    102 {
    103     int rc = SQLITE_OK;
    104     long longval;
    105     PY_LONG_LONG longlongval;
    106     const char* buffer;
    107     char* string;
    108     Py_ssize_t buflen;
    109     PyObject* stringval;
    110     parameter_type paramtype;
    111     char* c;
    112 
    113     if (parameter == Py_None) {
    114         rc = sqlite3_bind_null(self->st, pos);
    115         goto final;
    116     }
    117 
    118     if (PyInt_CheckExact(parameter)) {
    119         paramtype = TYPE_INT;
    120     } else if (PyLong_CheckExact(parameter)) {
    121         paramtype = TYPE_LONG;
    122     } else if (PyFloat_CheckExact(parameter)) {
    123         paramtype = TYPE_FLOAT;
    124     } else if (PyString_CheckExact(parameter)) {
    125         paramtype = TYPE_STRING;
    126     } else if (PyUnicode_CheckExact(parameter)) {
    127         paramtype = TYPE_UNICODE;
    128     } else if (PyBuffer_Check(parameter)) {
    129         paramtype = TYPE_BUFFER;
    130     } else if (PyInt_Check(parameter)) {
    131         paramtype = TYPE_INT;
    132     } else if (PyLong_Check(parameter)) {
    133         paramtype = TYPE_LONG;
    134     } else if (PyFloat_Check(parameter)) {
    135         paramtype = TYPE_FLOAT;
    136     } else if (PyString_Check(parameter)) {
    137         paramtype = TYPE_STRING;
    138     } else if (PyUnicode_Check(parameter)) {
    139         paramtype = TYPE_UNICODE;
    140     } else {
    141         paramtype = TYPE_UNKNOWN;
    142     }
    143 
    144     if (paramtype == TYPE_STRING && !allow_8bit_chars) {
    145         string = PyString_AS_STRING(parameter);
    146         for (c = string; *c != 0; c++) {
    147             if (*c & 0x80) {
    148                 PyErr_SetString(pysqlite_ProgrammingError, "You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.");
    149                 rc = -1;
    150                 goto final;
    151             }
    152         }
    153     }
    154 
    155     switch (paramtype) {
    156         case TYPE_INT:
    157             longval = PyInt_AsLong(parameter);
    158             rc = sqlite3_bind_int64(self->st, pos, (sqlite_int64)longval);
    159             break;
    160         case TYPE_LONG:
    161             longlongval = PyLong_AsLongLong(parameter);
    162             /* in the overflow error case, longlongval is -1, and an exception is set */
    163             rc = sqlite3_bind_int64(self->st, pos, (sqlite_int64)longlongval);
    164             break;
    165         case TYPE_FLOAT:
    166             rc = sqlite3_bind_double(self->st, pos, PyFloat_AsDouble(parameter));
    167             break;
    168         case TYPE_STRING:
    169             string = PyString_AS_STRING(parameter);
    170             rc = sqlite3_bind_text(self->st, pos, string, -1, SQLITE_TRANSIENT);
    171             break;
    172         case TYPE_UNICODE:
    173             stringval = PyUnicode_AsUTF8String(parameter);
    174             string = PyString_AsString(stringval);
    175             rc = sqlite3_bind_text(self->st, pos, string, -1, SQLITE_TRANSIENT);
    176             Py_DECREF(stringval);
    177             break;
    178         case TYPE_BUFFER:
    179             if (PyObject_AsCharBuffer(parameter, &buffer, &buflen) == 0) {
    180                 rc = sqlite3_bind_blob(self->st, pos, buffer, buflen, SQLITE_TRANSIENT);
    181             } else {
    182                 PyErr_SetString(PyExc_ValueError, "could not convert BLOB to buffer");
    183                 rc = -1;
    184             }
    185             break;
    186         case TYPE_UNKNOWN:
    187             rc = -1;
    188     }
    189 
    190 final:
    191     return rc;
    192 }
    193 
    194 /* returns 0 if the object is one of Python's internal ones that don't need to be adapted */
    195 static int _need_adapt(PyObject* obj)
    196 {
    197     if (pysqlite_BaseTypeAdapted) {
    198         return 1;
    199     }
    200 
    201     if (PyInt_CheckExact(obj) || PyLong_CheckExact(obj)
    202             || PyFloat_CheckExact(obj) || PyString_CheckExact(obj)
    203             || PyUnicode_CheckExact(obj) || PyBuffer_Check(obj)) {
    204         return 0;
    205     } else {
    206         return 1;
    207     }
    208 }
    209 
    210 void pysqlite_statement_bind_parameters(pysqlite_Statement* self, PyObject* parameters, int allow_8bit_chars)
    211 {
    212     PyObject* current_param;
    213     PyObject* adapted;
    214     const char* binding_name;
    215     int i;
    216     int rc;
    217     int num_params_needed;
    218     int num_params;
    219 
    220     Py_BEGIN_ALLOW_THREADS
    221     num_params_needed = sqlite3_bind_parameter_count(self->st);
    222     Py_END_ALLOW_THREADS
    223 
    224     if (PyTuple_CheckExact(parameters) || PyList_CheckExact(parameters) || (!PyDict_Check(parameters) && PySequence_Check(parameters))) {
    225         /* parameters passed as sequence */
    226         if (PyTuple_CheckExact(parameters)) {
    227             num_params = PyTuple_GET_SIZE(parameters);
    228         } else if (PyList_CheckExact(parameters)) {
    229             num_params = PyList_GET_SIZE(parameters);
    230         } else {
    231             num_params = PySequence_Size(parameters);
    232         }
    233         if (num_params != num_params_needed) {
    234             PyErr_Format(pysqlite_ProgrammingError, "Incorrect number of bindings supplied. The current statement uses %d, and there are %d supplied.",
    235                          num_params_needed, num_params);
    236             return;
    237         }
    238         for (i = 0; i < num_params; i++) {
    239             if (PyTuple_CheckExact(parameters)) {
    240                 current_param = PyTuple_GET_ITEM(parameters, i);
    241                 Py_XINCREF(current_param);
    242             } else if (PyList_CheckExact(parameters)) {
    243                 current_param = PyList_GET_ITEM(parameters, i);
    244                 Py_XINCREF(current_param);
    245             } else {
    246                 current_param = PySequence_GetItem(parameters, i);
    247             }
    248             if (!current_param) {
    249                 return;
    250             }
    251 
    252             if (!_need_adapt(current_param)) {
    253                 adapted = current_param;
    254             } else {
    255                 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)&pysqlite_PrepareProtocolType, NULL);
    256                 if (adapted) {
    257                     Py_DECREF(current_param);
    258                 } else {
    259                     PyErr_Clear();
    260                     adapted = current_param;
    261                 }
    262             }
    263 
    264             rc = pysqlite_statement_bind_parameter(self, i + 1, adapted, allow_8bit_chars);
    265             Py_DECREF(adapted);
    266 
    267             if (rc != SQLITE_OK) {
    268                 if (!PyErr_Occurred()) {
    269                     PyErr_Format(pysqlite_InterfaceError, "Error binding parameter %d - probably unsupported type.", i);
    270                 }
    271                 return;
    272             }
    273         }
    274     } else if (PyDict_Check(parameters)) {
    275         /* parameters passed as dictionary */
    276         for (i = 1; i <= num_params_needed; i++) {
    277             Py_BEGIN_ALLOW_THREADS
    278             binding_name = sqlite3_bind_parameter_name(self->st, i);
    279             Py_END_ALLOW_THREADS
    280             if (!binding_name) {
    281                 PyErr_Format(pysqlite_ProgrammingError, "Binding %d has no name, but you supplied a dictionary (which has only names).", i);
    282                 return;
    283             }
    284 
    285             binding_name++; /* skip first char (the colon) */
    286             if (PyDict_CheckExact(parameters)) {
    287                 current_param = PyDict_GetItemString(parameters, binding_name);
    288                 Py_XINCREF(current_param);
    289             } else {
    290                 current_param = PyMapping_GetItemString(parameters, (char*)binding_name);
    291             }
    292             if (!current_param) {
    293                 PyErr_Format(pysqlite_ProgrammingError, "You did not supply a value for binding %d.", i);
    294                 return;
    295             }
    296 
    297             if (!_need_adapt(current_param)) {
    298                 adapted = current_param;
    299             } else {
    300                 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)&pysqlite_PrepareProtocolType, NULL);
    301                 if (adapted) {
    302                     Py_DECREF(current_param);
    303                 } else {
    304                     PyErr_Clear();
    305                     adapted = current_param;
    306                 }
    307             }
    308 
    309             rc = pysqlite_statement_bind_parameter(self, i, adapted, allow_8bit_chars);
    310             Py_DECREF(adapted);
    311 
    312             if (rc != SQLITE_OK) {
    313                 if (!PyErr_Occurred()) {
    314                     PyErr_Format(pysqlite_InterfaceError, "Error binding parameter :%s - probably unsupported type.", binding_name);
    315                 }
    316                 return;
    317            }
    318         }
    319     } else {
    320         PyErr_SetString(PyExc_ValueError, "parameters are of unsupported type");
    321     }
    322 }
    323 
    324 int pysqlite_statement_recompile(pysqlite_Statement* self, PyObject* params)
    325 {
    326     const char* tail;
    327     int rc;
    328     char* sql_cstr;
    329     sqlite3_stmt* new_st;
    330 
    331     sql_cstr = PyString_AsString(self->sql);
    332 
    333     Py_BEGIN_ALLOW_THREADS
    334     rc = sqlite3_prepare(self->db,
    335                          sql_cstr,
    336                          -1,
    337                          &new_st,
    338                          &tail);
    339     Py_END_ALLOW_THREADS
    340 
    341     if (rc == SQLITE_OK) {
    342         /* The efficient sqlite3_transfer_bindings is only available in SQLite
    343          * version 3.2.2 or later. For older SQLite releases, that might not
    344          * even define SQLITE_VERSION_NUMBER, we do it the manual way.
    345          */
    346         #ifdef SQLITE_VERSION_NUMBER
    347         #if SQLITE_VERSION_NUMBER >= 3002002
    348         /* The check for the number of parameters is necessary to not trigger a
    349          * bug in certain SQLite versions (experienced in 3.2.8 and 3.3.4). */
    350         if (sqlite3_bind_parameter_count(self->st) > 0) {
    351             (void)sqlite3_transfer_bindings(self->st, new_st);
    352         }
    353         #endif
    354         #else
    355         statement_bind_parameters(self, params);
    356         #endif
    357 
    358         (void)sqlite3_finalize(self->st);
    359         self->st = new_st;
    360     }
    361 
    362     return rc;
    363 }
    364 
    365 int pysqlite_statement_finalize(pysqlite_Statement* self)
    366 {
    367     int rc;
    368 
    369     rc = SQLITE_OK;
    370     if (self->st) {
    371         Py_BEGIN_ALLOW_THREADS
    372         rc = sqlite3_finalize(self->st);
    373         Py_END_ALLOW_THREADS
    374         self->st = NULL;
    375     }
    376 
    377     self->in_use = 0;
    378 
    379     return rc;
    380 }
    381 
    382 int pysqlite_statement_reset(pysqlite_Statement* self)
    383 {
    384     int rc;
    385 
    386     rc = SQLITE_OK;
    387 
    388     if (self->in_use && self->st) {
    389         Py_BEGIN_ALLOW_THREADS
    390         rc = sqlite3_reset(self->st);
    391         Py_END_ALLOW_THREADS
    392 
    393         if (rc == SQLITE_OK) {
    394             self->in_use = 0;
    395         }
    396     }
    397 
    398     return rc;
    399 }
    400 
    401 void pysqlite_statement_mark_dirty(pysqlite_Statement* self)
    402 {
    403     self->in_use = 1;
    404 }
    405 
    406 void pysqlite_statement_dealloc(pysqlite_Statement* self)
    407 {
    408     int rc;
    409 
    410     if (self->st) {
    411         Py_BEGIN_ALLOW_THREADS
    412         rc = sqlite3_finalize(self->st);
    413         Py_END_ALLOW_THREADS
    414     }
    415 
    416     self->st = NULL;
    417 
    418     Py_XDECREF(self->sql);
    419 
    420     if (self->in_weakreflist != NULL) {
    421         PyObject_ClearWeakRefs((PyObject*)self);
    422     }
    423 
    424     Py_TYPE(self)->tp_free((PyObject*)self);
    425 }
    426 
    427 /*
    428  * Checks if there is anything left in an SQL string after SQLite compiled it.
    429  * This is used to check if somebody tried to execute more than one SQL command
    430  * with one execute()/executemany() command, which the DB-API and we don't
    431  * allow.
    432  *
    433  * Returns 1 if there is more left than should be. 0 if ok.
    434  */
    435 static int pysqlite_check_remaining_sql(const char* tail)
    436 {
    437     const char* pos = tail;
    438 
    439     parse_remaining_sql_state state = NORMAL;
    440 
    441     for (;;) {
    442         switch (*pos) {
    443             case 0:
    444                 return 0;
    445             case '-':
    446                 if (state == NORMAL) {
    447                     state  = LINECOMMENT_1;
    448                 } else if (state == LINECOMMENT_1) {
    449                     state = IN_LINECOMMENT;
    450                 }
    451                 break;
    452             case ' ':
    453             case '\t':
    454                 break;
    455             case '\n':
    456             case 13:
    457                 if (state == IN_LINECOMMENT) {
    458                     state = NORMAL;
    459                 }
    460                 break;
    461             case '/':
    462                 if (state == NORMAL) {
    463                     state = COMMENTSTART_1;
    464                 } else if (state == COMMENTEND_1) {
    465                     state = NORMAL;
    466                 } else if (state == COMMENTSTART_1) {
    467                     return 1;
    468                 }
    469                 break;
    470             case '*':
    471                 if (state == NORMAL) {
    472                     return 1;
    473                 } else if (state == LINECOMMENT_1) {
    474                     return 1;
    475                 } else if (state == COMMENTSTART_1) {
    476                     state = IN_COMMENT;
    477                 } else if (state == IN_COMMENT) {
    478                     state = COMMENTEND_1;
    479                 }
    480                 break;
    481             default:
    482                 if (state == COMMENTEND_1) {
    483                     state = IN_COMMENT;
    484                 } else if (state == IN_LINECOMMENT) {
    485                 } else if (state == IN_COMMENT) {
    486                 } else {
    487                     return 1;
    488                 }
    489         }
    490 
    491         pos++;
    492     }
    493 
    494     return 0;
    495 }
    496 
    497 PyTypeObject pysqlite_StatementType = {
    498         PyVarObject_HEAD_INIT(NULL, 0)
    499         MODULE_NAME ".Statement",                       /* tp_name */
    500         sizeof(pysqlite_Statement),                     /* tp_basicsize */
    501         0,                                              /* tp_itemsize */
    502         (destructor)pysqlite_statement_dealloc,         /* tp_dealloc */
    503         0,                                              /* tp_print */
    504         0,                                              /* tp_getattr */
    505         0,                                              /* tp_setattr */
    506         0,                                              /* tp_compare */
    507         0,                                              /* tp_repr */
    508         0,                                              /* tp_as_number */
    509         0,                                              /* tp_as_sequence */
    510         0,                                              /* tp_as_mapping */
    511         0,                                              /* tp_hash */
    512         0,                                              /* tp_call */
    513         0,                                              /* tp_str */
    514         0,                                              /* tp_getattro */
    515         0,                                              /* tp_setattro */
    516         0,                                              /* tp_as_buffer */
    517         Py_TPFLAGS_DEFAULT | Py_TPFLAGS_HAVE_WEAKREFS,  /* tp_flags */
    518         0,                                              /* tp_doc */
    519         0,                                              /* tp_traverse */
    520         0,                                              /* tp_clear */
    521         0,                                              /* tp_richcompare */
    522         offsetof(pysqlite_Statement, in_weakreflist),   /* tp_weaklistoffset */
    523         0,                                              /* tp_iter */
    524         0,                                              /* tp_iternext */
    525         0,                                              /* tp_methods */
    526         0,                                              /* tp_members */
    527         0,                                              /* tp_getset */
    528         0,                                              /* tp_base */
    529         0,                                              /* tp_dict */
    530         0,                                              /* tp_descr_get */
    531         0,                                              /* tp_descr_set */
    532         0,                                              /* tp_dictoffset */
    533         (initproc)0,                                    /* tp_init */
    534         0,                                              /* tp_alloc */
    535         0,                                              /* tp_new */
    536         0                                               /* tp_free */
    537 };
    538 
    539 extern int pysqlite_statement_setup_types(void)
    540 {
    541     pysqlite_StatementType.tp_new = PyType_GenericNew;
    542     return PyType_Ready(&pysqlite_StatementType);
    543 }
    544