Skip to content

gh-84943: Add support for 'directonly' and 'innocuous' flags for user-defined functions #20398

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
wants to merge 1 commit into from
Closed

gh-84943: Add support for 'directonly' and 'innocuous' flags for user-defined functions #20398

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
32 changes: 30 additions & 2 deletions Doc/library/sqlite3.rst
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -343,7 +343,7 @@ Connection Objects
:meth:`~Cursor.executescript` method with the given *sql_script*, and
returns the cursor.

.. method:: create_function(name, num_params, func, *, deterministic=False)
.. method:: create_function(name, num_params, func, *, deterministic=False, directonly=False, innocous=False)

Creates a user-defined function that you can later use from within SQL
statements under the function name *name*. *num_params* is the number of
Expand All @@ -355,18 +355,40 @@ Connection Objects
SQLite 3.8.3 or higher, :exc:`NotSupportedError` will be raised if used
with older versions.

The *innocuous* flag means that the function is unlikely to cause problems
even if misused. An innocuous function should have no side effects and
should not depend on any values other than its input parameters.
Developers are advised to avoid using the *innocuous* flag for
application-defined functions unless the function has been carefully
audited and found to be free of potentially security-adverse side-effects
and information-leaks. This flag is supported by SQLite 3.31.0 or higher.
:exc:`NotSupportedError` will be raised if used with older SQLite versions.

The *directonly* flag means that the function may only be invoked from
top-level SQL, and cannot be used in VIEWs or TRIGGERs nor in schema
structures such as CHECK constraints, DEFAULT clauses, expression indexes,
partial indexes, or generated columns. The *directonly* flag is a security
feature which is recommended for all application-defined SQL functions,
and especially for functions that have side-effects or that could
potentially leak sensitive information. This flag is supported by SQLite
3.31.0 or higher. :exc:`NotSupportedError` will be raised if used with
older SQLite versions.

The function can return any of the types supported by SQLite: bytes, str, int,
float and ``None``.

.. versionchanged:: 3.8
The *deterministic* parameter was added.

.. versionchanged:: 3.10
The *innocuous* and *directonly* parameters were added.

Example:

.. literalinclude:: ../includes/sqlite3/md5func.py


.. method:: create_aggregate(name, num_params, aggregate_class)
.. method:: create_aggregate(name, num_params, aggregate_class, directonly=False, innocuous=False)

Creates a user-defined aggregate function.

Expand All @@ -378,6 +400,12 @@ Connection Objects
The ``finalize`` method can return any of the types supported by SQLite:
bytes, str, int, float and ``None``.

See :func:`create_function` for a description of the *innocuous* and
*directonly* parameters.

.. versionchanged:: 3.10
The *innocuous* and *directonly* parameters were added.

Example:

.. literalinclude:: ../includes/sqlite3/mysumaggr.py
Expand Down
75 changes: 75 additions & 0 deletions Lib/sqlite3/test/userfunctions.py
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -316,6 +316,39 @@ def CheckFuncDeterministicKeywordOnly(self):
with self.assertRaises(TypeError):
self.con.create_function("deterministic", 0, int, True)

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or higher")
def CheckFuncNonInnocuousInTrustedEnv(self):
mock = unittest.mock.Mock(return_value=None)
self.con.create_function("noninnocuous", 0, mock, innocuous=False)
self.con.execute("pragma trusted_schema = 0")
self.con.execute("drop view if exists notallowed")
self.con.execute("create view notallowed as select noninnocuous() = noninnocuous()")
with self.assertRaises(sqlite.OperationalError) as cm:
self.con.execute("select * from notallowed")
self.assertEqual(str(cm.exception), 'unsafe use of noninnocuous()')

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or higher")
def CheckFuncInnocuousInTrustedEnv(self):
mock = unittest.mock.Mock(return_value=None)
self.con.create_function("innocuous", 0, mock, innocuous=True)
self.con.execute("pragma trusted_schema = 0")
self.con.execute("drop view if exists allowed")
self.con.execute("create view allowed as select innocuous() = innocuous()")
self.con.execute("select * from allowed")
self.assertEqual(mock.call_count, 2)

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or higher")
def CheckFuncDirectOnly(self):
mock = unittest.mock.Mock(return_value=None)
self.con.create_function("directonly", 0, mock, directonly=True)
self.con.execute("pragma trusted_schema = 1")
self.con.execute("drop view if exists notallowed")
self.con.execute("select directonly() = directonly()")
self.assertEqual(mock.call_count, 2)
self.con.execute("create view notallowed as select directonly() = directonly()")
with self.assertRaises(sqlite.OperationalError) as cm:
self.con.execute("select * from notallowed")
self.assertEqual(str(cm.exception), 'unsafe use of directonly()')

class AggregateTests(unittest.TestCase):
def setUp(self):
Expand All @@ -341,6 +374,9 @@ def setUp(self):
self.con.create_aggregate("checkType", 2, AggrCheckType)
self.con.create_aggregate("checkTypes", -1, AggrCheckTypes)
self.con.create_aggregate("mysum", 1, AggrSum)
if sqlite.sqlite_version_info >= (3, 31, 0):
self.con.create_aggregate("mysumInnocuous", 1, AggrSum, innocuous=True)
self.con.create_aggregate("mysumDirectOnly", 1, AggrSum, directonly=True)

def tearDown(self):
#self.cur.close()
Expand Down Expand Up @@ -429,6 +465,45 @@ def CheckAggrCheckAggrSum(self):
val = cur.fetchone()[0]
self.assertEqual(val, 60)

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or newer")
def CheckAggrNonInnocuous(self):
cur = self.con.cursor()
cur.execute("pragma trusted_schema = 0")
cur.execute("delete from test")
cur.execute("drop view if exists notallowed")
cur.execute("insert into test(i) values (?)", (10,))
cur.execute("create view notallowed as select mysum(i) from test")
with self.assertRaises(sqlite.OperationalError) as cm:
cur.execute("select * from notallowed")
self.assertEqual(str(cm.exception), 'unsafe use of mysum()')

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or newer")
def CheckAggrInnocuous(self):
cur = self.con.cursor()
cur.execute("pragma trusted_schema = 0")
cur.execute("delete from test")
cur.execute("drop view if exists allowed")
cur.executemany("insert into test(i) values (?)", [(10,), (20,), (30,)])
cur.execute("create view allowed as select mysumInnocuous(i) from test")
cur.execute("select * from allowed")
val = cur.fetchone()[0]
self.assertEqual(val, 60)

@unittest.skipIf(sqlite.sqlite_version_info < (3, 31, 0), "Requires SQLite 3.31.0 or newer")
def CheckAggrDirectOnly(self):
cur = self.con.cursor()
cur.execute("pragma trusted_schema = 1")
cur.execute("delete from test")
cur.execute("drop view if exists notallowed")
cur.executemany("insert into test(i) values (?)", [(10,), (20,), (30,)])
cur.execute("create view notallowed as select mysumDirectOnly(i) from test")
with self.assertRaises(sqlite.OperationalError) as cm:
cur.execute("select * from notallowed")
self.assertEqual(str(cm.exception), 'unsafe use of mysumdirectonly()')
cur.execute("select mysumDirectOnly(i) from test")
val = cur.fetchone()[0]
self.assertEqual(val, 60)

class AuthorizerTests(unittest.TestCase):
@staticmethod
def authorizer_cb(action, arg1, arg2, dbname, source):
Expand Down
2 changes: 2 additions & 0 deletions Misc/NEWS.d/next/Library/2020-05-25-21-55-24.bpo-40766.bh9Ze5.rst
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,2 @@
Add support for `SQLITE_INNOCUOUS` and `SQLITE_DIRECTONLY` flags in
:mod:`sqlite3`.
81 changes: 68 additions & 13 deletions Modules/_sqlite/clinic/connection.c.h
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -94,7 +94,8 @@ pysqlite_connection_rollback(pysqlite_Connection *self, PyObject *Py_UNUSED(igno
}

PyDoc_STRVAR(pysqlite_connection_create_function__doc__,
"create_function($self, /, name, narg, func, *, deterministic=False)\n"
"create_function($self, /, name, narg, func, *, deterministic=False,\n"
" directonly=False, innocuous=False)\n"
"--\n"
"\n"
"Creates a new function. Non-standard.");
Expand All @@ -105,20 +106,23 @@ PyDoc_STRVAR(pysqlite_connection_create_function__doc__,
static PyObject *
pysqlite_connection_create_function_impl(pysqlite_Connection *self,
const char *name, int narg,
PyObject *func, int deterministic);
PyObject *func, int deterministic,
int directonly, int innocuous);

static PyObject *
pysqlite_connection_create_function(pysqlite_Connection *self, PyObject *const *args, Py_ssize_t nargs, PyObject *kwnames)
{
PyObject *return_value = NULL;
static const char * const _keywords[] = {"name", "narg", "func", "deterministic", NULL};
static const char * const _keywords[] = {"name", "narg", "func", "deterministic", "directonly", "innocuous", NULL};
static _PyArg_Parser _parser = {NULL, _keywords, "create_function", 0};
PyObject *argsbuf[4];
PyObject *argsbuf[6];
Py_ssize_t noptargs = nargs + (kwnames ? PyTuple_GET_SIZE(kwnames) : 0) - 3;
const char *name;
int narg;
PyObject *func;
int deterministic = 0;
int directonly = 0;
int innocuous = 0;

args = _PyArg_UnpackKeywords(args, nargs, NULL, kwnames, &_parser, 3, 3, 0, argsbuf);
if (!args) {
Expand All @@ -145,19 +149,38 @@ pysqlite_connection_create_function(pysqlite_Connection *self, PyObject *const *
if (!noptargs) {
goto skip_optional_kwonly;
}
deterministic = PyObject_IsTrue(args[3]);
if (deterministic < 0) {
if (args[3]) {
deterministic = PyObject_IsTrue(args[3]);
if (deterministic < 0) {
goto exit;
}
if (!--noptargs) {
goto skip_optional_kwonly;
}
}
if (args[4]) {
directonly = PyObject_IsTrue(args[4]);
if (directonly < 0) {
goto exit;
}
if (!--noptargs) {
goto skip_optional_kwonly;
}
}
innocuous = PyObject_IsTrue(args[5]);
if (innocuous < 0) {
goto exit;
}
skip_optional_kwonly:
return_value = pysqlite_connection_create_function_impl(self, name, narg, func, deterministic);
return_value = pysqlite_connection_create_function_impl(self, name, narg, func, deterministic, directonly, innocuous);

exit:
return return_value;
}

PyDoc_STRVAR(pysqlite_connection_create_aggregate__doc__,
"create_aggregate($self, /, name, n_arg, aggregate_class)\n"
"create_aggregate($self, /, name, n_arg, aggregate_class, *,\n"
" deterministic=False, directonly=False, innocuous=False)\n"
"--\n"
"\n"
"Creates a new aggregate. Non-standard.");
Expand All @@ -168,18 +191,24 @@ PyDoc_STRVAR(pysqlite_connection_create_aggregate__doc__,
static PyObject *
pysqlite_connection_create_aggregate_impl(pysqlite_Connection *self,
const char *name, int n_arg,
PyObject *aggregate_class);
PyObject *aggregate_class,
int deterministic, int directonly,
int innocuous);

static PyObject *
pysqlite_connection_create_aggregate(pysqlite_Connection *self, PyObject *const *args, Py_ssize_t nargs, PyObject *kwnames)
{
PyObject *return_value = NULL;
static const char * const _keywords[] = {"name", "n_arg", "aggregate_class", NULL};
static const char * const _keywords[] = {"name", "n_arg", "aggregate_class", "deterministic", "directonly", "innocuous", NULL};
static _PyArg_Parser _parser = {NULL, _keywords, "create_aggregate", 0};
PyObject *argsbuf[3];
PyObject *argsbuf[6];
Py_ssize_t noptargs = nargs + (kwnames ? PyTuple_GET_SIZE(kwnames) : 0) - 3;
const char *name;
int n_arg;
PyObject *aggregate_class;
int deterministic = 0;
int directonly = 0;
int innocuous = 0;

args = _PyArg_UnpackKeywords(args, nargs, NULL, kwnames, &_parser, 3, 3, 0, argsbuf);
if (!args) {
Expand All @@ -203,7 +232,33 @@ pysqlite_connection_create_aggregate(pysqlite_Connection *self, PyObject *const
goto exit;
}
aggregate_class = args[2];
return_value = pysqlite_connection_create_aggregate_impl(self, name, n_arg, aggregate_class);
if (!noptargs) {
goto skip_optional_kwonly;
}
if (args[3]) {
deterministic = PyObject_IsTrue(args[3]);
if (deterministic < 0) {
goto exit;
}
if (!--noptargs) {
goto skip_optional_kwonly;
}
}
if (args[4]) {
directonly = PyObject_IsTrue(args[4]);
if (directonly < 0) {
goto exit;
}
if (!--noptargs) {
goto skip_optional_kwonly;
}
}
innocuous = PyObject_IsTrue(args[5]);
if (innocuous < 0) {
goto exit;
}
skip_optional_kwonly:
return_value = pysqlite_connection_create_aggregate_impl(self, name, n_arg, aggregate_class, deterministic, directonly, innocuous);

exit:
return return_value;
Expand Down Expand Up @@ -719,4 +774,4 @@ pysqlite_connection_exit(pysqlite_Connection *self, PyObject *const *args, Py_ss
#ifndef PYSQLITE_CONNECTION_LOAD_EXTENSION_METHODDEF
#define PYSQLITE_CONNECTION_LOAD_EXTENSION_METHODDEF
#endif /* !defined(PYSQLITE_CONNECTION_LOAD_EXTENSION_METHODDEF) */
/*[clinic end generated code: output=7cb13d491a5970aa input=a9049054013a1b77]*/
/*[clinic end generated code: output=33bcda978b5bc004 input=a9049054013a1b77]*/
Loading