1/* statement.c - the statement type
2 *
3 * Copyright (C) 2005-2010 Gerhard Häring <[email protected]>
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 "util.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_LONG,
45 TYPE_FLOAT,
46 TYPE_UNICODE,
47 TYPE_BUFFER,
48 TYPE_UNKNOWN
49} parameter_type;
50
51pysqlite_Statement *
52pysqlite_statement_create(pysqlite_Connection *connection, PyObject *sql)
53{
54 const char* tail;
55 int rc;
56 const char* sql_cstr;
57 Py_ssize_t sql_cstr_len;
58 const char* p;
59
60 assert(PyUnicode_Check(sql));
61
62 sql_cstr = PyUnicode_AsUTF8AndSize(sql, &sql_cstr_len);
63 if (sql_cstr == NULL) {
64 PyErr_Format(pysqlite_Warning,
65 "SQL is of wrong type ('%s'). Must be string.",
66 Py_TYPE(sql)->tp_name);
67 return NULL;
68 }
69 if (strlen(sql_cstr) != (size_t)sql_cstr_len) {
70 PyErr_SetString(PyExc_ValueError,
71 "the query contains a null character");
72 return NULL;
73 }
74
75 pysqlite_Statement *self = PyObject_GC_New(pysqlite_Statement,
76 pysqlite_StatementType);
77 if (self == NULL) {
78 return NULL;
79 }
80
81 self->db = connection->db;
82 self->st = NULL;
83 self->sql = Py_NewRef(sql);
84 self->in_use = 0;
85 self->is_dml = 0;
86 self->in_weakreflist = NULL;
87
88 /* Determine if the statement is a DML statement.
89 SELECT is the only exception. See #9924. */
90 for (p = sql_cstr; *p != 0; p++) {
91 switch (*p) {
92 case ' ':
93 case '\r':
94 case '\n':
95 case '\t':
96 continue;
97 }
98
99 self->is_dml = (PyOS_strnicmp(p, "insert", 6) == 0)
100 || (PyOS_strnicmp(p, "update", 6) == 0)
101 || (PyOS_strnicmp(p, "delete", 6) == 0)
102 || (PyOS_strnicmp(p, "replace", 7) == 0);
103 break;
104 }
105
106 Py_BEGIN_ALLOW_THREADS
107 rc = sqlite3_prepare_v2(self->db,
108 sql_cstr,
109 -1,
110 &self->st,
111 &tail);
112 Py_END_ALLOW_THREADS
113
114 PyObject_GC_Track(self);
115
116 if (rc != SQLITE_OK) {
117 _pysqlite_seterror(self->db, NULL);
118 goto error;
119 }
120
121 if (rc == SQLITE_OK && pysqlite_check_remaining_sql(tail)) {
122 (void)sqlite3_finalize(self->st);
123 self->st = NULL;
124 PyErr_SetString(pysqlite_Warning,
125 "You can only execute one statement at a time.");
126 goto error;
127 }
128
129 return self;
130
131error:
132 Py_DECREF(self);
133 return NULL;
134}
135
136int pysqlite_statement_bind_parameter(pysqlite_Statement* self, int pos, PyObject* parameter)
137{
138 int rc = SQLITE_OK;
139 const char *string;
140 Py_ssize_t buflen;
141 parameter_type paramtype;
142
143 if (parameter == Py_None) {
144 rc = sqlite3_bind_null(self->st, pos);
145 goto final;
146 }
147
148 if (PyLong_CheckExact(parameter)) {
149 paramtype = TYPE_LONG;
150 } else if (PyFloat_CheckExact(parameter)) {
151 paramtype = TYPE_FLOAT;
152 } else if (PyUnicode_CheckExact(parameter)) {
153 paramtype = TYPE_UNICODE;
154 } else if (PyLong_Check(parameter)) {
155 paramtype = TYPE_LONG;
156 } else if (PyFloat_Check(parameter)) {
157 paramtype = TYPE_FLOAT;
158 } else if (PyUnicode_Check(parameter)) {
159 paramtype = TYPE_UNICODE;
160 } else if (PyObject_CheckBuffer(parameter)) {
161 paramtype = TYPE_BUFFER;
162 } else {
163 paramtype = TYPE_UNKNOWN;
164 }
165
166 switch (paramtype) {
167 case TYPE_LONG: {
168 sqlite_int64 value = _pysqlite_long_as_int64(parameter);
169 if (value == -1 && PyErr_Occurred())
170 rc = -1;
171 else
172 rc = sqlite3_bind_int64(self->st, pos, value);
173 break;
174 }
175 case TYPE_FLOAT: {
176 double value = PyFloat_AsDouble(parameter);
177 if (value == -1 && PyErr_Occurred()) {
178 rc = -1;
179 }
180 else {
181 rc = sqlite3_bind_double(self->st, pos, value);
182 }
183 break;
184 }
185 case TYPE_UNICODE:
186 string = PyUnicode_AsUTF8AndSize(parameter, &buflen);
187 if (string == NULL)
188 return -1;
189 if (buflen > INT_MAX) {
190 PyErr_SetString(PyExc_OverflowError,
191 "string longer than INT_MAX bytes");
192 return -1;
193 }
194 rc = sqlite3_bind_text(self->st, pos, string, (int)buflen, SQLITE_TRANSIENT);
195 break;
196 case TYPE_BUFFER: {
197 Py_buffer view;
198 if (PyObject_GetBuffer(parameter, &view, PyBUF_SIMPLE) != 0) {
199 PyErr_SetString(PyExc_ValueError, "could not convert BLOB to buffer");
200 return -1;
201 }
202 if (view.len > INT_MAX) {
203 PyErr_SetString(PyExc_OverflowError,
204 "BLOB longer than INT_MAX bytes");
205 PyBuffer_Release(&view);
206 return -1;
207 }
208 rc = sqlite3_bind_blob(self->st, pos, view.buf, (int)view.len, SQLITE_TRANSIENT);
209 PyBuffer_Release(&view);
210 break;
211 }
212 case TYPE_UNKNOWN:
213 rc = -1;
214 }
215
216final:
217 return rc;
218}
219
220/* returns 0 if the object is one of Python's internal ones that don't need to be adapted */
221static int _need_adapt(PyObject* obj)
222{
223 if (pysqlite_BaseTypeAdapted) {
224 return 1;
225 }
226
227 if (PyLong_CheckExact(obj) || PyFloat_CheckExact(obj)
228 || PyUnicode_CheckExact(obj) || PyByteArray_CheckExact(obj)) {
229 return 0;
230 } else {
231 return 1;
232 }
233}
234
235void pysqlite_statement_bind_parameters(pysqlite_Statement* self, PyObject* parameters)
236{
237 PyObject* current_param;
238 PyObject* adapted;
239 const char* binding_name;
240 int i;
241 int rc;
242 int num_params_needed;
243 Py_ssize_t num_params;
244
245 Py_BEGIN_ALLOW_THREADS
246 num_params_needed = sqlite3_bind_parameter_count(self->st);
247 Py_END_ALLOW_THREADS
248
249 if (PyTuple_CheckExact(parameters) || PyList_CheckExact(parameters) || (!PyDict_Check(parameters) && PySequence_Check(parameters))) {
250 /* parameters passed as sequence */
251 if (PyTuple_CheckExact(parameters)) {
252 num_params = PyTuple_GET_SIZE(parameters);
253 } else if (PyList_CheckExact(parameters)) {
254 num_params = PyList_GET_SIZE(parameters);
255 } else {
256 num_params = PySequence_Size(parameters);
257 if (num_params == -1) {
258 return;
259 }
260 }
261 if (num_params != num_params_needed) {
262 PyErr_Format(pysqlite_ProgrammingError,
263 "Incorrect number of bindings supplied. The current "
264 "statement uses %d, and there are %zd supplied.",
265 num_params_needed, num_params);
266 return;
267 }
268 for (i = 0; i < num_params; i++) {
269 if (PyTuple_CheckExact(parameters)) {
270 PyObject *item = PyTuple_GET_ITEM(parameters, i);
271 current_param = Py_NewRef(item);
272 } else if (PyList_CheckExact(parameters)) {
273 PyObject *item = PyList_GetItem(parameters, i);
274 current_param = Py_XNewRef(item);
275 } else {
276 current_param = PySequence_GetItem(parameters, i);
277 }
278 if (!current_param) {
279 return;
280 }
281
282 if (!_need_adapt(current_param)) {
283 adapted = current_param;
284 } else {
285 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)pysqlite_PrepareProtocolType, current_param);
286 Py_DECREF(current_param);
287 if (!adapted) {
288 return;
289 }
290 }
291
292 rc = pysqlite_statement_bind_parameter(self, i + 1, adapted);
293 Py_DECREF(adapted);
294
295 if (rc != SQLITE_OK) {
296 if (!PyErr_Occurred()) {
297 PyErr_Format(pysqlite_InterfaceError, "Error binding parameter %d - probably unsupported type.", i);
298 }
299 return;
300 }
301 }
302 } else if (PyDict_Check(parameters)) {
303 /* parameters passed as dictionary */
304 for (i = 1; i <= num_params_needed; i++) {
305 PyObject *binding_name_obj;
306 Py_BEGIN_ALLOW_THREADS
307 binding_name = sqlite3_bind_parameter_name(self->st, i);
308 Py_END_ALLOW_THREADS
309 if (!binding_name) {
310 PyErr_Format(pysqlite_ProgrammingError, "Binding %d has no name, but you supplied a dictionary (which has only names).", i);
311 return;
312 }
313
314 binding_name++; /* skip first char (the colon) */
315 binding_name_obj = PyUnicode_FromString(binding_name);
316 if (!binding_name_obj) {
317 return;
318 }
319 if (PyDict_CheckExact(parameters)) {
320 PyObject *item = PyDict_GetItemWithError(parameters, binding_name_obj);
321 current_param = Py_XNewRef(item);
322 } else {
323 current_param = PyObject_GetItem(parameters, binding_name_obj);
324 }
325 Py_DECREF(binding_name_obj);
326 if (!current_param) {
327 if (!PyErr_Occurred() || PyErr_ExceptionMatches(PyExc_LookupError)) {
328 PyErr_Format(pysqlite_ProgrammingError, "You did not supply a value for binding parameter :%s.", binding_name);
329 }
330 return;
331 }
332
333 if (!_need_adapt(current_param)) {
334 adapted = current_param;
335 } else {
336 adapted = pysqlite_microprotocols_adapt(current_param, (PyObject*)pysqlite_PrepareProtocolType, current_param);
337 Py_DECREF(current_param);
338 if (!adapted) {
339 return;
340 }
341 }
342
343 rc = pysqlite_statement_bind_parameter(self, i, adapted);
344 Py_DECREF(adapted);
345
346 if (rc != SQLITE_OK) {
347 if (!PyErr_Occurred()) {
348 PyErr_Format(pysqlite_InterfaceError, "Error binding parameter :%s - probably unsupported type.", binding_name);
349 }
350 return;
351 }
352 }
353 } else {
354 PyErr_SetString(PyExc_ValueError, "parameters are of unsupported type");
355 }
356}
357
358int pysqlite_statement_finalize(pysqlite_Statement* self)
359{
360 int rc;
361
362 rc = SQLITE_OK;
363 if (self->st) {
364 Py_BEGIN_ALLOW_THREADS
365 rc = sqlite3_finalize(self->st);
366 Py_END_ALLOW_THREADS
367 self->st = NULL;
368 }
369
370 self->in_use = 0;
371
372 return rc;
373}
374
375int pysqlite_statement_reset(pysqlite_Statement* self)
376{
377 int rc;
378
379 rc = SQLITE_OK;
380
381 if (self->in_use && self->st) {
382 Py_BEGIN_ALLOW_THREADS
383 rc = sqlite3_reset(self->st);
384 Py_END_ALLOW_THREADS
385
386 if (rc == SQLITE_OK) {
387 self->in_use = 0;
388 }
389 }
390
391 return rc;
392}
393
394void pysqlite_statement_mark_dirty(pysqlite_Statement* self)
395{
396 self->in_use = 1;
397}
398
399static void
400stmt_dealloc(pysqlite_Statement *self)
401{
402 PyTypeObject *tp = Py_TYPE(self);
403 PyObject_GC_UnTrack(self);
404 if (self->in_weakreflist != NULL) {
405 PyObject_ClearWeakRefs((PyObject*)self);
406 }
407 if (self->st) {
408 Py_BEGIN_ALLOW_THREADS
409 sqlite3_finalize(self->st);
410 Py_END_ALLOW_THREADS
411 self->st = 0;
412 }
413 tp->tp_clear((PyObject *)self);
414 tp->tp_free(self);
415 Py_DECREF(tp);
416}
417
418static int
419stmt_clear(pysqlite_Statement *self)
420{
421 Py_CLEAR(self->sql);
422 return 0;
423}
424
425static int
426stmt_traverse(pysqlite_Statement *self, visitproc visit, void *arg)
427{
428 Py_VISIT(Py_TYPE(self));
429 Py_VISIT(self->sql);
430 return 0;
431}
432
433/*
434 * Checks if there is anything left in an SQL string after SQLite compiled it.
435 * This is used to check if somebody tried to execute more than one SQL command
436 * with one execute()/executemany() command, which the DB-API and we don't
437 * allow.
438 *
439 * Returns 1 if there is more left than should be. 0 if ok.
440 */
441static int pysqlite_check_remaining_sql(const char* tail)
442{
443 const char* pos = tail;
444
445 parse_remaining_sql_state state = NORMAL;
446
447 for (;;) {
448 switch (*pos) {
449 case 0:
450 return 0;
451 case '-':
452 if (state == NORMAL) {
453 state = LINECOMMENT_1;
454 } else if (state == LINECOMMENT_1) {
455 state = IN_LINECOMMENT;
456 }
457 break;
458 case ' ':
459 case '\t':
460 break;
461 case '\n':
462 case 13:
463 if (state == IN_LINECOMMENT) {
464 state = NORMAL;
465 }
466 break;
467 case '/':
468 if (state == NORMAL) {
469 state = COMMENTSTART_1;
470 } else if (state == COMMENTEND_1) {
471 state = NORMAL;
472 } else if (state == COMMENTSTART_1) {
473 return 1;
474 }
475 break;
476 case '*':
477 if (state == NORMAL) {
478 return 1;
479 } else if (state == LINECOMMENT_1) {
480 return 1;
481 } else if (state == COMMENTSTART_1) {
482 state = IN_COMMENT;
483 } else if (state == IN_COMMENT) {
484 state = COMMENTEND_1;
485 }
486 break;
487 default:
488 if (state == COMMENTEND_1) {
489 state = IN_COMMENT;
490 } else if (state == IN_LINECOMMENT) {
491 } else if (state == IN_COMMENT) {
492 } else {
493 return 1;
494 }
495 }
496
497 pos++;
498 }
499
500 return 0;
501}
502
503static PyMemberDef stmt_members[] = {
504 {"__weaklistoffset__", T_PYSSIZET, offsetof(pysqlite_Statement, in_weakreflist), READONLY},
505 {NULL},
506};
507static PyType_Slot stmt_slots[] = {
508 {Py_tp_members, stmt_members},
509 {Py_tp_dealloc, stmt_dealloc},
510 {Py_tp_traverse, stmt_traverse},
511 {Py_tp_clear, stmt_clear},
512 {0, NULL},
513};
514
515static PyType_Spec stmt_spec = {
516 .name = MODULE_NAME ".Statement",
517 .basicsize = sizeof(pysqlite_Statement),
518 .flags = (Py_TPFLAGS_DEFAULT | Py_TPFLAGS_HAVE_GC |
519 Py_TPFLAGS_IMMUTABLETYPE | Py_TPFLAGS_DISALLOW_INSTANTIATION),
520 .slots = stmt_slots,
521};
522PyTypeObject *pysqlite_StatementType = NULL;
523
524int
525pysqlite_statement_setup_types(PyObject *module)
526{
527 pysqlite_StatementType = (PyTypeObject *)PyType_FromModuleAndSpec(module, &stmt_spec, NULL);
528 if (pysqlite_StatementType == NULL) {
529 return -1;
530 }
531 return 0;
532}
533