SQLOPS module
Admin Guide
Section titled “Admin Guide”Overview
Section titled “Overview”SQLops (SQL-operations) modules implements a set of script functions for generic SQL standard queries (raw or structure queries). It also provides a dedicated set of functions for DB manipulation (loading/storing/removing) of user AVPs (preferences).
Dependencies
Section titled “Dependencies”OpenSIPS Modules
Section titled “OpenSIPS Modules”The following modules must be loaded before this module:
- a database module
External Libraries or Applications
Section titled “External Libraries or Applications”The following libraries or applications must be installed before running OpenSIPS with this module loaded:
- None
Exported Parameters
Section titled “Exported Parameters”db_url (string)
Section titled “db_url (string)”DB URL for database connection. As the module allows the usage of multiple DBs (DB URLs), the actual DB URL may be preceded by an reference number. This reference number is to be passed to AVPOPS function that what to explicitly use this DB connection. If no reference number is given, 0 is assumed - this is the default DB URL.
This parameter is optional, it’s default value being NULL.
...# default URLmodparam("sqlops","db_url","mysql://user:passwd@host/database")# an additional DB URLmodparam("sqlops","db_url","1 postgres://user:passwd@host2/opensips")...usr_table (string)
Section titled “usr_table (string)”DB table to be used for user preferences (AVPs)
This parameter is optional, it’s default value being “usr_preferences”.
...modparam("sqlops","usr_table","avptable")...db_scheme (string)
Section titled “db_scheme (string)”Definition of a DB scheme to be used for accessing a non-standard User Preference -like table.
Definition of a DB scheme. Scheme syntax is:
- db_scheme = name’:‘element[’;‘element]*
- element =
- ‘uuid_col=‘string
- ‘username_col=‘string
- ‘domain_col=‘string
- ‘value_col=‘string
- ‘value_type=’(‘integer’|‘string’)
- ‘table=‘string
Default value is “NULL”.
...modparam("sqlops","db_scheme","scheme1:table=subscriber;uuid_col=uuid;value_col=first_name")...use_domain (boolean)
Section titled “use_domain (boolean)”If the domain part of the a SIP URI should be used for identifying an AVP in DB operations.
Default value is true (enabled).
...modparam("sqlops", "use_domain", true)...ps_id_max_buf_len (integer)
Section titled “ps_id_max_buf_len (integer)”The maximum size of the buffer used to build the query IDs which are used for managing the Prepare Statements when comes to the “sql_select|update|insert|replace|delete()” functions
If the size is exceeded (when trying to build the PS query ID), the PS support will be dropped for the query. If set to 0, the PS support will be completly disabled.
Default value is 1024.
...modparam("sqlops","ps_id_max_buf_len", 2048)...bigint_to_str (int)
Section titled “bigint_to_str (int)”Controls bigint conversion. By default bigint values are returned as int. If the value stored in bigint is out of the int range, by enabling bigint to string conversion, the bigint value will be returned as string.
Default value is “0”.
...# Return bigint as stringmodparam("sqlops","bigint_to_str",1)...uuid_column (string)
Section titled “uuid_column (string)”Name of column containing the uuid (unique user id).
Default value is “uuid”.
...modparam("sqlops","uuid_column","uuid")...username_column (string)
Section titled “username_column (string)”Name of column containing the username.
Default value is “username”.
...modparam("sqlops","username_column","username")...domain_column (string)
Section titled “domain_column (string)”Name of column containing the domain name.
Default value is “domain”.
...modparam("sqlops","domain_column","domain")...attribute_column (string)
Section titled “attribute_column (string)”Name of column containing the attribute name (AVP name).
Default value is “attribute”.
...modparam("sqlops","attribute_column","attribute")...value_column (string)
Section titled “value_column (string)”Name of column containing the AVP value.
Default value is “value”.
...modparam("sqlops","value_column","value")...type_column (string)
Section titled “type_column (string)”Name of column containing the AVP type.
Default value is “type”.
...modparam("sqlops","type_column","type")...Exported Functions
Section titled “Exported Functions”sql_query(query, [res_col_avps], [db_id])
Section titled “sql_query(query, [res_col_avps], [db_id])”Make a database query and store the result in AVPs.
The meaning and usage of the parameters:
- query (string) - must be a valid SQL query. The parameter can contain pseudo-variables. You must escape any pseudo-variables manually to prevent SQL injection attacks. You can use the existing transformations escape.common and unescape.common to escape and unescape the content of any pseudo-variable. Failing to escape the variables used in the query makes you vulnerable to SQL injection, e.g. make it possible for an outside attacker to alter your database content. The function returns true if the query was successful, -2 in case the query returned an empty result set, and -1 for all other types of errors.
- res_col_avps (string, optional, no expand) - a list with AVP names where
to store the result. The format is
“$avp(name1);$avp(name2);…”. If this parameter
is omitted, the result is stored in
“$avp(1);$avp(2);…”. If the result consists of
multiple rows, then multiple AVPs with corresponding names will
be added. The value type of the AVP (string or integer) will
be derived from the type of the columns. If the value in the
database is NULL, the returned avp will
be a string with the
value. - db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the “db_url” module parameter. It can be either a constant, or a string/int variable.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
...sql_query("SELECT password, ha1 FROM subscriber WHERE username='$tu'", "$avp(pass);$avp(hash)");sql_query("DELETE FROM subscriber");sql_query("DELETE FROM subscriber", , 2);
$avp(id) = 2;sql_query("DELETE FROM subscriber", , $avp(id));...sql_query_one(query, [res_col_vars], [db_id])
Section titled “sql_query_one(query, [res_col_vars], [db_id])”Similar to sql query, it makes a generic raw database query and returns the results, but with the following differences:
- returns only one row - even if the query results in a multi row result, only the first row will be returned to script.
- return variables are not limited to AVPs - the variables for returning the query result may any kind of variable, of course, as time as it is writeable. NOTE that the number of return vairable MUST match (as number) the number of returned columns. If less variables are provided, the query will fail.
- NULL is returned - any a DB NULL
value resulting from the query will be pushed as NULL indicator
(and NOT as
string) to the script variables.
This function can be used from any type of route.
...sql_query_one("SELECT password, ha1 FROM subscriber WHERE username='$tU'", "$var(pass);$var(hash)");# $var(pass) or $var(hash) may be NULL if the corresponding columns# are not populated...sql_query_one("SELECT value, type FROM usr_preferences WHERE username='$fU' and attribute='cfna'", "$var(cf_uri);$var(type)");# the above query will return only one row, even if there are multiple `cfna`# attributes for the user...sql_select([columns],table,[filter],[order],[res_col_avps], [db_id])
Section titled “sql_select([columns],table,[filter],[order],[res_col_avps], [db_id])”Function to perform a structured (not raw) SQL SELECT operation. The query is performed via OpenSIPS internal SQL interface, taking advantages of the prepared-statements support (if the db backend provides something like that). The selected columns are returned into a set of AVPs (one to one matching the selected columns).
The function returns true if the query was successful, -2 in case the query returned an empty result set, and -1 for all other types of errors.
The meaning and usage of the parameters:
- columns (string,optional) - JSON formated string holding an array of columns to be returned by the select. Ex: ”[“col1”,“col2”]”. If missing, a ”*” (all columns) select will be performed.
- table (string, mandatory) - the name of the table to be queried.
- filter (string, optional) - JSON
formated string holding the “where” filter of the query. This
must be an array of (column, operator,value) pairs. The
exact JSON syntax of such a pair is
”{“column”:{“operator”:“value”}}”.; operators
may be
>,<,=,!=or custom string; The values may be string, integer ornull. To simplify the usage with the=operator, you can use ”{“column”:“value”}” If missing, all rows will be selected. - order (string, optional) - the name of the column to oder by (only ascending).
- res_col_avps (string, optional, no expand) - a list with AVP names where
to store the result. The format is
“$avp(name1);$avp(name2);…”. If this parameter
is omitted, the result is stored in
“$avp(1);$avp(2);…”. If the result consists of
multiple rows, then multiple AVPs with corresponding names will
be added. The value type of the AVP (string or integer) will
be derived from the type of the columns. If the value in the
database is NULL, the returned avp will
be a string with the
value. - db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
...sql_select('["password","ha1"]', 'subscriber', '[ {"username": "$tu"}, {"domain": {"!=", null}}]', , '$avp(pass);$avp(hash)');...sql_select_one([columns],table,[filter],[order],[res_col_vars], [db_id])
Section titled “sql_select_one([columns],table,[filter],[order],[res_col_vars], [db_id])”Similar to sql select, it makes a SELECT SQL query and returns the results, but with the following differences:
- returns only one row - even if the query results in a multi row result, only the first row will be returned to script.
- return variables are not limited to AVPs - the variables for returning the query result may any kind of variable, of course, as time as it is writeable. NOTE that the number of return vairable MUST match (as number) the number of returned columns. If less variables are provided, the query will fail.
- NULL is returned - any a DB NULL
value resulting from the query will be pushed as NULL indicator
(and NOT as
string) to the script variables.
This function can be used from any type of route.
...sql_select_one('["value","type"]', 'usr_preferences', '[ {"username": "$tu"}, {"attribute": "cfna"}]', , '$var(cf_uri);$var(type)');# the above query will return only one row, even if there are multiple `cfna`# attributes for the user...sql_update(columns,table,[filter],[db_id])
Section titled “sql_update(columns,table,[filter],[db_id])”Function to perform a structured (not raw) SQL UPDATE operation. IMPORTANT: please see all the general notes from the sql select function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
- columns (string,mandatory) - JSON formated string holding an array of (column,value) pairs to be updated by the query. Ex: ”[{“col1”:“val1”},{“col2”:“val1”}]”.
- table (string, mandatory) - the name of the table to be queried.
- filter (string, optional) - JSON
formated string holding the “where” filter of the query. This
must be an array of (column, operator,value) pairs. The
exact JSON syntax of such a pair is
”{“column”:{“operator”:“value”}}”.; operators
may be
>,<,=,!=or custom string; The values may be string, integer ornull. To simplify the usage with the=operator, you can use ”{“column”:“value”}” If missing, all rows will be updated. - db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
...sql_update( '[{"password":"my_secret"}]', 'subscriber', '[{"username": "$tu"}]');...sql_insert(table,columns,[db_id])
Section titled “sql_insert(table,columns,[db_id])”Function to perform a structured (not raw) SQL INSERT operation. IMPORTANT: please see all the general notes from the sql select function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
- table (string, mandatory) - the name of the table to be queried.
- columns (string,mandatory) - JSON formated string holding an array of (column,value) pairs to be inserted. Ex: ”[{“col1”:“val1”},{“col2”:“val1”}]”.
- db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
...sql_insert( 'cc_agents', '[{"agentid":"agentX"},{"skills":"info"},{"location":null},{"msrp_location":"sip:agentX@opensips.com"},{"msrp_max_sessions":2}]' );...sql_delete(table,[filter],[db_id])
Section titled “sql_delete(table,[filter],[db_id])”Function to perform a structured (not raw) SQL DELETE operation. IMPORTANT: please see all the general notes from the sql select function.
The function returns true if the query was successful.
The meaning and usage of the parameters:
- table (string, mandatory) - the name of the table to delete from.
- filter (string, optional) - JSON
formated string holding the “where” filter of the query. This
must be an array of (column, operator,value) pairs. The
exact JSON syntax of such a pair is
”{“column”:{“operator”:“value”}}”.; operators
may be
>,<,=,!=or custom string; The values may be string, integer ornull. To simplify the usage with the=operator, you can use ”{“column”:“value”}” If missing, all rows will be updated. - db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the db url module parameter. It can be either a constant, or a string/int variable.
This function can be used from any type of route.
...sql_delete( 'subscriber', '[{"username": "$tu"}]');...sql_replace(table,columns,[db_id])
Section titled “sql_replace(table,columns,[db_id])”Function very similar to sql insert function, but performing an SQL REPLACE operation instead. Note that not all SQL backend in OpenSIPS may support a REPLACE operation.
The function returns true if the query was successful.
sql_avp_load(source, name, [db_id], [prefix]])
Section titled “sql_avp_load(source, name, [db_id], [prefix]])”Loads from DB into memory the AVPs corresponding to the given source. If given, it sets the script flags for loaded AVPs. It returns true if it loaded some values in AVPs, false otherwise (db error, no avp loaded …).
AVPs may be preceded by an optional prefix, in order to avoid some conflicts.
Meaning of the parameters is as follows:
- source (string, no expand) - what info is used for
identifying the AVPs. Parameter syntax:
- source = (pvar|str_value) [’/’(‘username’|‘domain’|‘uri’|‘uuid’)])
- pvar = any pseudo variable defined in OpenSIPS. If the pvar is $ru (request uri), $fu (from uri), $tu (to uri) or $ou (original uri), then the implicit flag is ‘uri’. Otherwise, the implicit flag is ‘uuid’.
- name (string, no expand) - which AVPs will be loaded
from DB into memory. Parameter syntax is:
- name = avp_spec[’/‘(table_name|’$‘db_scheme)]
- db_id (int, optional) - reference to a defined DB URL (a numerical id) - see the “db_url” module parameter.
- prefix (string, optional) - static string which will precede the names of the AVPs populated by this function.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
...sql_avp_load("$fu", "$avp(678)");sql_avp_load("$ru/domain", "i/domain_preferences");sql_avp_load("$avp(uuid)", "$avp(404fwd)/fwd_table");sql_avp_load("$ru", "$avp(123)/$some_scheme");
# use DB URL id 3sql_avp_load("$ru", "$avp(1)", 3);
# precede all loaded AVPs by the "caller_" prefixsql_avp_load("$ru", "$avp(100)", , "caller_");xlog("Loaded: $avp(caller_100)\n");...sql_avp_store(source, name, [db_id])
Section titled “sql_avp_store(source, name, [db_id])”Stores to DB the AVPs corresponding to the given source.
The meaning and usage of the parameters are identical as for sql_avp_load(source, name) function. Please refer to its description.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
...sql_avp_store("$tu", "$avp(678)");sql_avp_store("$ru/username", "$avp(email)");# use DB URL id 3sql_avp_store("$ru", "$avp(1)", 3);...sql_avp_delete(source, name, [db_id])
Section titled “sql_avp_delete(source, name, [db_id])”Deletes from DB the AVPs corresponding to the given source.
The meaning and usage of the parameters are identical as for sql_avp_load(source, name) function. Please refer to its description.
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
...sql_avp_delete("$tu", "$avp(678)");sql_avp_delete("$ru/username", "$avp(email)");sql_avp_delete("$avp(uuid)", "$avp(404fwd)/fwd_table");# use DB URL id 3sql_avp_delete("$ru", "$avp(1)", 3);...Exported Asynchronous Functions
Section titled “Exported Asynchronous Functions”sql_query(query, [dest], [db_id])
Section titled “sql_query(query, [dest], [db_id])”This function takes the same parameters and behaves identically to sql query, but asynchronously (after launching the query, the current SIP worker pauses the execution of the current SIP message until the result is available and attempts to process more SIP traffic).
This function can be used from REQUEST_ROUTE, FAILURE_ROUTE, BRANCH_ROUTE, LOCAL_ROUTE and ONREPLY_ROUTE.
...{.../* Example of a slow MySQL query - it should take around 5 seconds */async( sql_query( "SELECT table_name, table_version, SLEEP(0.1) from version", "$avp(tb_name); $avp(tb_ver); $avp(retcode)"), my_resume_route);/* script execution is halted right after the async() call */}
/* We will be called when data is ready - meanwhile, the worker is free */route [my_resume_route]{ xlog("Results: \n$(avp(tb_name)[*])\n-------------------\n$(avp(tb_ver)[*])\n-------------------\n$(avp(retcode)[*])\n");}...sql_query_one(query, [dest], [db_id])
Section titled “sql_query_one(query, [dest], [db_id])”This function takes the same parameters and behaves identically to sql query one, but asynchronously (after launching the query, the current SIP worker pauses the execution of the current SIP message until the result is available and attempts to process more SIP traffic).
This function can be used from any route.
...{.../* Example of a slow MySQL query - it should take around 5 seconds */async( sql_query_one( "SELECT table_name, table_version, SLEEP(0.1) from version", "$var(tb_name); $var(tb_ver); $var(retcode)"), my_resume_route);/* script execution is halted right after the async() call */}
/* We will be called when data is ready - meanwhile, the worker is free */route [my_resume_route]{ xlog("Result: $var(tb_name) | $var(tb_ver) | $(var(retcode)\n");}...Contributors
Section titled “Contributors”By Commit Statistics
Section titled “By Commit Statistics”Top contributors by DevScore(1), authored commits(2) and lines added/removed(3)
| # | Name | DevScore | Commits | Lines++ | Lines— |
|---|---|---|---|---|---|
| 1. | Bogdan-Andrei Iancu (@bogdan-iancu) | 373 | 77 | 10823 | 12311 |
| 2. | Daniel-Constantin Mierla (@miconda) | 111 | 44 | 3163 | 2394 |
| 3. | Liviu Chircu (@liviuchircu) | 59 | 29 | 1275 | 1107 |
| 4. | Elena-Ramona Modroiu | 54 | 11 | 4111 | 461 |
| 5. | Razvan Crainea (@razvancrainea) | 22 | 15 | 162 | 259 |
| 6. | Stefan Darius | 19 | 5 | 1060 | 249 |
| 7. | Elena-Ramona Modroiu | 18 | 5 | 1063 | 204 |
| 8. | Henning Westerholt (@henningw) | 12 | 8 | 114 | 135 |
| 9. | Vlad Paiu (@vladpaiu) | 9 | 7 | 40 | 3 |
| 10. | Ionut Ionita (@ionutrazvanionita) | 8 | 5 | 226 | 58 |
All remaining contributors: Norman Brandinger (@NormB), Kobi Eshun (@ekobi), Andrei Pelinescu-Onciul, Maksym Sobolyev (@sobomax), Anca Vamanu, Ovidiu Sas (@ovidiusas), Vlad Patrascu (@rvlad-patrascu), Klaus Darilion, John Burke (@john08burke), Andrey Vorobiev (@andrey-vorobiev), Nick Altmann (@nikbyte), Olle E. Johansson, Kennard White, Julián Moreno Patiño, Walter Doekes (@wdoekes), Konstantin Bokarius, Peter Lemenkov (@lemenkov), Sergio Gutierrez, Andreas Granig, Edson Gellert Schubert.
(1) DevScore = author_commits + author_lines_added / (project_lines_added / project_commits) + author_lines_deleted / (project_lines_deleted / project_commits)
(2) including any documentation-related commits, excluding merge commits
(3) ignoring whitespace edits, renamed files and auto-generated files
By Commit Activity
Section titled “By Commit Activity”| # | Name | Commit Activity |
|---|---|---|
| 1. | Stefan Darius | Jun 2026 - Jul 2026 |
| 2. | Razvan Crainea (@razvancrainea) | Jun 2011 - Jun 2026 |
| 3. | Liviu Chircu (@liviuchircu) | Mar 2013 - Apr 2026 |
| 4. | Bogdan-Andrei Iancu (@bogdan-iancu) | Jun 2005 - Apr 2026 |
| 5. | Maksym Sobolyev (@sobomax) | Oct 2022 - Apr 2025 |
| 6. | Norman Brandinger (@NormB) | Aug 2006 - Mar 2025 |
| 7. | Nick Altmann (@nikbyte) | Feb 2025 - Feb 2025 |
| 8. | Ovidiu Sas (@ovidiusas) | Jul 2015 - Apr 2024 |
| 9. | Vlad Paiu (@vladpaiu) | Jun 2011 - Jul 2023 |
| 10. | John Burke (@john08burke) | Jun 2022 - Jun 2022 |
All remaining contributors: Vlad Patrascu (@rvlad-patrascu), Peter Lemenkov (@lemenkov), Andrey Vorobiev (@andrey-vorobiev), Julián Moreno Patiño, Ionut Ionita (@ionutrazvanionita), Walter Doekes (@wdoekes), Anca Vamanu, Kennard White, Sergio Gutierrez, Kobi Eshun (@ekobi), Henning Westerholt (@henningw), Olle E. Johansson, Daniel-Constantin Mierla (@miconda), Konstantin Bokarius, Edson Gellert Schubert, Elena-Ramona Modroiu, Klaus Darilion, Andreas Granig, Andrei Pelinescu-Onciul, Elena-Ramona Modroiu.
(1) including any documentation-related commits, excluding merge commits
Documentation
Section titled “Documentation”Contributors
Section titled “Contributors”Last edited by: Razvan Crainea (@razvancrainea), Liviu Chircu (@liviuchircu), Ovidiu Sas (@ovidiusas), Bogdan-Andrei Iancu (@bogdan-iancu), John Burke (@john08burke), Peter Lemenkov (@lemenkov), Ionut Ionita (@ionutrazvanionita), Vlad Paiu (@vladpaiu), Anca Vamanu, Norman Brandinger (@NormB), Kobi Eshun (@ekobi), Henning Westerholt (@henningw), Daniel-Constantin Mierla (@miconda), Konstantin Bokarius, Edson Gellert Schubert, Elena-Ramona Modroiu, Klaus Darilion, Andrei Pelinescu-Onciul, Elena-Ramona Modroiu.
License
Section titled “License”All documentation files (i.e. .md extension) are licensed under the Creative Common License 4.0