Security/Guides/SQL Queries and 3rd Party Packages

SQL queries

Connecting your application and database layers can pose security risks. Notably, SQL injection (SQLi). Below is an outline of the do's and dont's of executing SQL queries in MediaWiki.

Never correct

MediaWiki developers MUST NOT directly execute SQL queries through PHP's database extension functions (such as mysqli_query() or pg_send_query()). While this is not so much for security reasons, it helps with maintainability. Using the MediaWiki database wrappers helps ensure your queries end up at the correct database (which may or may not be the same as where the wiki itself is stored), and handles things like table prefixes.

Directly build a SQL string in one of these provided functions makes the developer responsible for escaping the SQL parameters themselves. Otherwise, applications can be susceptible to en:SQL injection.^[1]

$userName = $_GET['username'];
mysqli_query( "SELECT user_id FROM user WHERE user_name = '$userName'", $conn ); // NEVER DO THIS

In this very simple example, no validation is done on the input, and as such, passing something like?username=' OR '1'='1 could result in more rows being dumped than expected, as the SQL becomes:

SELECT user_id FROM user WHERE user_name = '' OR '1'='1'"

Even when you do use the MediaWiki wrappers, you should be careful about quoting values manually, and should use appropriate functions such as ->addQuotes() and ->addIdentifierQuotes() when necessary. These examples below would still be potentially vulnerable to SQLi. It is generally better to assume the input is coming from an untrusted source, and as such, allow escaping to happen as necessary. It's better to quote and it not be needed, than not quote because it's not currently needed, and someone add a different usage of your code later, and pass in untrusted input, opening up an SQLi vector.

$dbw = MediaWiki\MediaWikiServices::getInstance()->getConnectionProvider()->getPrimaryDatabase();
$res = $db->query( "SELECT user_id FROM user WHERE user_name = '$userName'" ); // NEVER DO THIS

Most queries have dedicated wrappers that you should used. For SELECT queries, don't use the ->query(), use ->select().

$res = $db->select(
	'user',
	[ 'user_id' ],
	[ "user_name = '$userName'" ], // NEVER DO THIS
	__METHOD__
);

This is still wrong though!

Usually correct

Most of the time, developers SHOULD use existing wrapper functions like IDatabase::select() or IDatabase::insert() to perform SQL queries. When passing in parameters to these wrapper functions, it is important to know when you need to manually use IDatabase::addQuotes() on raw user data. IDatabase::addQuotes() correctly escapes the provided input (such as values in WHERE statements) to help prevent SQL injection vectors.

$res = $db->select(
	'user',
	[ 'user_id' ],
	[ 'user_name = ' . $dbw->addQuotes( $userName ) ],
	__METHOD__
);

For simple queries (column = 'value' or column IN ('value1', 'value2')), the database wrappers will take care of escaping input for you. Manually calling addQuotes this is unnecessary. You can pass an int, an array or a string in the same way, and the code will deal with it. For a single value, column = 'value' will be used, and when passed an array IN will be used instead.

$res = $db->select(
	'user',
	[ 'user_id' ],
	[ 'user_name' => $userName ],
	__METHOD__
);

If you want to do other queries in statements such as WHERE with operators like < or >, you will need to do some manual escaping yourself, such as done in the first example.

$res = $db->select(
	'user',
	[ 'user_id' ],
	[ 'user_name > ' . $dbw->addQuotes( $userNameStart ), 'user_name < ' . $dbw->addQuotes( $userNameEnd ) ],
	__METHOD__
);

SelectQueryBuilder

MediaWiki version:

≥ 1.35

As of MediaWiki 1.35, developers MAY use the SelectQueryBuilder class to create SQL SELECT statements. This class allows function chaining so SQL queries are more easily readable and don't require specifically formatted input parameters (like IDatabase::select() does).

$queryBuilder = new SelectQueryBuilder( $dbw )
	->from( 'user' )
	->fields( 'user_id' )
	->where( [ 'user_name' => $userName ] );

$res = $queryBuilder->fetchResultSet();

Similar to the older wrappers, parameters to SelectQueryBuilder wrapper functions such as SelectQueryBuilder::where() still may need to be escaped via IDatabase::addQuotes() before being passed in, if doing something more than a simple equality (such as = or IN).

$queryBuilder = new SelectQueryBuilder( $dbw )
	->from( 'user' )
	->fields( 'user_id' )
	->where( [
		'user_name > ' . $dbw->addQuotes( $userNameStart ),
		'user_name < ' . $dbw->addQuotes( $userNameEnd )
	] );

$res = $queryBuilder->fetchResultSet();

Rarely correct

Rarely, developers MAY use IDatabase::query() to execute a custom SQL query, one that does not fit within the parameters of the IDatabase wrapper functions or the SelectQueryBuilder. This is useful for queries that are explicitly DBMS-dependent and are unsupported by the query wrappers such as CREATE TABLE.

3rd-party packages

Installing 3rd party packages in MediaWiki and other WMF projects can be incredibly useful, but also has the opportunity to introduce security risks.

NPM

MediaWiki and other WMF projects SHOULD NOT directly install (via npm install) non-WMF NPM packages in production environments (WMF packages being anything in the NPM Wikimedia organization). When you install an NPM package you not only install itself, but the tens or hundreds of other dependencies it relies on.

NPM dependency tree showing the large number of packages actually installed when installing a single NPM package

. At the end of the day, you're introducing untrusted code onto production machines that could do anything from steal ssh keys to access secure files on the hard drive.^[2] Many WMF projects do however have npm packages listed in their devDependencies. These packages are used solely for local development (e.g. testing or linting).

Ideally, WMF projects try to avoid using external libraries as much as possible by relying on in-house packages and/or writing the functionality ourselves. However, sometimes that is not possible. If using a 3rd party package however is necessary, developers SHOULD instead commit the 3rd party package source code to the project itself. Since a majority of security risks are introduced at install time, it is better to avoid doing the install on project startup.

MediaWiki core for example, has a series of NPM packages loaded using ResourceLoader. We can see an example of how this is done by looking at how MediaWiki uses moment, a JavaScript date/time library. Instead of listing moment in package.json, the source code lives under /resources/lib/moment. MediaWiki can now require('moment') just like it would any other NPM package. Committing the source code allows the 3rd party code to be audited, and prevents untrusted code from being run on production machines. A full registry of 3rd party packages used in MediaWiki core are listed in resources/lib/foreign-resources.yaml file.

Composer

MediaWiki and other WMF projects do however have Composer packages installed in production environments. These are generally considered safer due to having less dependencies on average and are audited more frequently by the Security Team.

Contacting the Security Team

Still have questions? Don't be shy! The Security Team welcomes any and all questions you may have. When in doubt, just get in touch with the Security Team.

References

[1] SQL injection attacks

[2] Protect yourself from NPM

[1]

[2]