/Modules/_sqlite/statement.c

  1/* statement.c - the statement type
  2 *
  3 * Copyright (C) 2005-2007 Gerhard Häring <gh@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 */
 32static int pysqlite_check_remaining_sql(const char* tail);
 33
 34typedef enum {
 35    LINECOMMENT_1,
 36    IN_LINECOMMENT,
 37    COMMENTSTART_1,
 38    IN_COMMENT,
 39    COMMENTEND_1,
 40    NORMAL
 41} parse_remaining_sql_state;
 42
 43typedef 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
 53int 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
101int 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
190final:
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 */
195static 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
210void 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
324int 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
365int 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
382int 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
401void pysqlite_statement_mark_dirty(pysqlite_Statement* self)
402{
403    self->in_use = 1;
404}
405
406void 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 */
435static 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
497PyTypeObject 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
539extern int pysqlite_statement_setup_types(void)
540{
541    pysqlite_StatementType.tp_new = PyType_GenericNew;
542    return PyType_Ready(&pysqlite_StatementType);
543}