ImportRunSql.php
<?php
namespace Automattic\WooCommerce\Blueprint\Importers;
use Automattic\WooCommerce\Blueprint\StepProcessor;
use Automattic\WooCommerce\Blueprint\StepProcessorResult;
use Automattic\WooCommerce\Blueprint\Steps\RunSql;
use Automattic\WooCommerce\Blueprint\UsePluginHelpers;
use Automattic\WooCommerce\Blueprint\UseWPFunctions;
/**
* Processes SQL execution steps in the Blueprint.
*
* Handles the execution of SQL queries with safety checks to prevent
* unauthorized modifications to sensitive WordPress data.
*
* @package Automattic\WooCommerce\Blueprint\Importers
*/
class ImportRunSql implements StepProcessor {
use UsePluginHelpers;
use UseWPFunctions;
/**
* List of allowed SQL query types.
*
* @var array
*/
private const ALLOWED_QUERY_TYPES = array(
'INSERT',
'UPDATE',
'REPLACE INTO',
);
/**
* Process the SQL execution step.
*
* Validates and executes the SQL query while ensuring:
* 1. Only allowed query types are executed
* 2. No modifications to admin users or roles
* 3. No unauthorized changes to user capabilities
*
* @param object $schema The schema containing the SQL query to execute.
* @return StepProcessorResult The result of the SQL execution.
*/
public function process( $schema ): StepProcessorResult {
global $wpdb;
$result = StepProcessorResult::success( RunSql::get_step_name() );
$sql = trim( $schema->sql->contents );
// Check if the query type is allowed.
if ( ! $this->is_allowed_query_type( $sql ) ) {
$result->add_error(
sprintf(
'Only %s queries are allowed.',
implode( ', ', self::ALLOWED_QUERY_TYPES )
)
);
return $result;
}
// Check for SQL comments that might be hiding malicious code.
if ( $this->contains_suspicious_comments( $sql ) ) {
$result->add_error( 'SQL query contains suspicious comment patterns.' );
return $result;
}
// Detect SQL injection patterns.
if ( $this->contains_sql_injection_patterns( $sql ) ) {
$result->add_error( 'SQL query contains potential injection patterns.' );
return $result;
}
// Check if the query affects protected tables.
if ( $this->affects_protected_tables( $sql ) ) {
$result->add_error( 'Modifications to admin users or roles are not allowed.' );
return $result;
}
// Check if the query affects user capabilities in wp_options.
if ( $this->affects_user_capabilities( $sql ) ) {
$result->add_error( 'Modifications to user roles or capabilities are not allowed.' );
return $result;
}
$wpdb->suppress_errors( true );
$wpdb->query( 'START TRANSACTION' );
try {
$query_result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$last_error = $wpdb->last_error;
if ( $last_error ) {
$wpdb->query( 'ROLLBACK' );
$result->add_error( 'Error executing SQL: ' . $last_error );
} else {
$wpdb->query( 'COMMIT' );
$result->add_debug( "Executed SQL ({$schema->sql->name}): Affected {$query_result} rows" );
}
} catch ( \Throwable $e ) {
$wpdb->query( 'ROLLBACK' );
$result->add_error( "Exception executing SQL: {$e->getMessage()}" );
}
return $result;
}
/**
* Returns the class name of the step this processor handles.
*
* @return string The class name of the step this processor handles.
*/
public function get_step_class(): string {
return RunSql::class;
}
/**
* Check if the current user has the required capabilities for this step.
*
* @param object $schema The schema to process.
*
* @return bool True if the user has the required capabilities. False otherwise.
*/
public function check_step_capabilities( $schema ): bool {
if ( ! current_user_can( 'manage_options' ) ) {
return false;
}
if ( ! current_user_can( 'edit_posts' ) ) {
return false;
}
if ( ! current_user_can( 'edit_users' ) ) {
return false;
}
return true;
}
/**
* Check if the SQL query type is allowed.
*
* @param string $sql_content The SQL query to check.
* @return bool True if the query type is allowed, false otherwise.
*/
private function is_allowed_query_type( string $sql_content ): bool {
$uppercase_sql_content = strtoupper( trim( $sql_content ) );
foreach ( self::ALLOWED_QUERY_TYPES as $query_type ) {
if ( 0 === stripos( $uppercase_sql_content, $query_type ) ) {
return true;
}
}
return false;
}
/**
* Check for suspicious comment patterns that might hide malicious code.
*
* This method detects various types of SQL comments that might be used
* to hide malicious SQL commands or bypass security filters.
*
* @param string $sql_content The SQL query to check.
* @return bool True if suspicious comments found, false otherwise.
*/
private function contains_suspicious_comments( string $sql_content ): bool {
// Quick check if there are any comments at all before running regex.
if (
strpos( $sql_content, '--' ) === false &&
strpos( $sql_content, '/*' ) === false &&
strpos( $sql_content, '#' ) === false
) {
return false;
}
// List of potentially dangerous SQL commands to check for in comments.
$dangerous_commands = array(
'DELETE',
'DROP',
'ALTER',
'CREATE',
'TRUNCATE',
'GRANT',
'REVOKE',
'EXEC',
'EXECUTE',
'CALL',
'INTO OUTFILE',
'INTO DUMPFILE',
'LOAD_FILE',
'LOAD DATA',
'BENCHMARK',
'SLEEP',
'INFORMATION_SCHEMA',
'USER\\(',
'DATABASE\\(',
'SCHEMA\\(',
);
$dangerous_pattern = implode( '|', $dangerous_commands );
// Check for SQL comments that might be hiding malicious code.
$patterns = array(
// Single-line comments (-- style) containing dangerous commands.
'/--.*?(' . $dangerous_pattern . ')/i',
// Single-line comments (# style) containing dangerous commands.
'/#.*?(' . $dangerous_pattern . ')/i',
// Multi-line comments hiding dangerous commands.
'/\/\*.*?(' . $dangerous_pattern . ').*?\*\//is',
// MySQL-specific execution comments (version-specific code execution).
'/\/\*![0-9]*.*?\*\//',
);
foreach ( $patterns as $pattern ) {
if ( preg_match( $pattern, $sql_content ) ) {
return true;
}
}
return false;
}
/**
* Check for common SQL injection patterns.
*
* @param string $sql_content The SQL query to check.
* @return bool True if potential injection patterns found, false otherwise.
*/
private function contains_sql_injection_patterns( string $sql_content ): bool {
$patterns = array(
'/UNION\s+(?:ALL\s+)?SELECT/i', // UNION-based injections.
'/OR\s+1\s*=\s*1/i', // OR 1=1 condition.
'/AND\s+0\s*=\s*0/i', // AND 0=0 condition.
'/;\s*--/i', // Inline comment terminations.
'/SLEEP\s*\(/i', // Time-based injections.
'/BENCHMARK\s*\(/i', // Benchmark-based injections.
'/LOAD_FILE\s*\(/i', // File access.
'/INTO\s+OUTFILE/i', // File write.
'/INTO\s+DUMPFILE/i', // File dump.
'/CREATE\s+(?:TEMPORARY\s+)?TABLE/i', // Table creation.
'/DROP\s+TABLE/i', // Table deletion.
'/ALTER\s+TABLE/i', // Table alteration.
'/INFORMATION_SCHEMA/i', // Database metadata access.
'/EXEC\s*\(/i', // Stored procedure execution.
'/SCHEMA_NAME/i', // Schema access.
'/DATABASE\(\)/i', // Current database name.
'/CHR\s*\(/i', // Character function for evasion.
'/CHAR\s*\(/i', // Character function for evasion.
'/FROM\s+mysql\./i', // Direct MySQL system table access.
'/FROM\s+information_schema\./i', // Direct information schema access.
);
foreach ( $patterns as $pattern ) {
if ( preg_match( $pattern, $sql_content ) ) {
return true;
}
}
return false;
}
/**
* Check if the SQL query affects protected user tables.
*
* @param string $sql_content The SQL query to check.
* @return bool True if the query affects protected tables, false otherwise.
*/
private function affects_protected_tables( string $sql_content ): bool {
global $wpdb;
$protected_tables = array(
$wpdb->users,
$wpdb->usermeta,
);
foreach ( $protected_tables as $table ) {
if ( preg_match( '/\b' . preg_quote( $table, '/' ) . '\b/i', $sql_content ) ) {
return true;
}
}
return false;
}
/**
* Check if the SQL query affects user capabilities in wp_options.
*
* @param string $sql_content The SQL query to check.
* @return bool True if the query affects user capabilities, false otherwise.
*/
private function affects_user_capabilities( string $sql_content ): bool {
global $wpdb;
// Check if the query affects user capabilities in wp_options.
if ( stripos( $sql_content, $wpdb->prefix . 'options' ) !== false ) {
$option_patterns = array(
'user_roles',
'capabilities',
'wp_user_',
'role_',
'administrator',
);
foreach ( $option_patterns as $pattern ) {
if ( stripos( $sql_content, $pattern ) !== false ) {
return true;
}
}
}
return false;
}
}
