CODEWITHSUNDEEP
mysqlregexstored-proceduressql-injection
CodeMonkeyMajor
CodeMonkeyMajor

Reputation: 21

Preventing SQL Injection in MySQL by limiting usable characters and using REGEXP()

I'm looking for a way to prevent SQL Injection without using prepared statements. I know prepared statements are the correct way to prevent SQL Injection, but MySQL doesn't allow parameters to be used in an ALTER USER prepared statement, so I need an alternative:

# Will not work
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`()
BEGIN
    SET @InputVal = 'NewPassword';
    SET @SQLStr = 'ALTER USER \'SomeUser\'@\'localhost\' IDENTIFIED BY ?';
    
    PREPARE SQLStatement FROM @SQLStr;
    EXECUTE SQLStatement USING @InputVal;
    DEALLOCATE PREPARE SQLStatement;
END

The specific goal is to create a stored procedure that allows users to change their password. I have it working with the assumption that alphanumeric inputs cannot lead to SQL injection (presumably a fair enough assumption to make, but I'm open to correction since I've seen some very creative SQL Injections using things like backspace characters):

CREATE DEFINER=`root`@`localhost` PROCEDURE `ChangeCurrentUserPassword`(IN NewPassword VARCHAR(30))
BEGIN
    SET @NewPassword = NewPassword;
    
    IF @NewPassword REGEXP '^[a-z0-9]{1,}$' THEN
        SET @SQLStr = CONCAT('ALTER USER \'', Substring_Index(USER(),'@',1), '\'@\'localhost\' IDENTIFIED BY \'', NewPassword, '\';');
        
        PREPARE SQLStatement FROM @SQLStr;
        EXECUTE SQLStatement;
        DEALLOCATE PREPARE SQLStatement;
    ELSE
        # Non-alphanumeric (or blank) password.
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must be alpha-numeric';
    END IF;
END

The above does work as-intended and is presumably secure (aside from the fact that it allows single-character passwords - it was just a throw-together to test the logic), but I don't like the fact that it limits passwords to alpha-numeric only. I would like to as many non-alpha-numeric characters as possible that I can add into the REGEXP statement.

I have seen a lot of other threads asking about using REGEXP to detect SQL injection, but for each of them they were asking about detecting from any raw input. The key difference here is that rather than trying to detect SQL Injection attempts, I'm trying to limit the allowable characters so that SQL injection is not possible in the first place.

Does anyone know of a list of characters that can be included but still safely prevents SQL Injection?

Side-note: I'm happy to go a whole new approach to this procedure if there is a better way that still allows a procedure. I know I can UPDATE the mysql.user table directly which will allow for prepared statements being used properly, but I'd like to avoid updating system tables directly - I feel statements like ALTER USER exist purely because direct updates should be avoided where possible.

Sorry this was long-winded. Thanks for taking the time to get to this point!

Upvotes: 2

Views: 392

Answers (1)

Booboo
Booboo

Reputation: 44108

Read the answers to SQL injection that gets around mysql_real_escape_string(), especially this one.

This is an old answer that references deprecated PHP function mysql_real_escape_string. This has been replaced by mysqli_real_escape_string, which encodes the following special characters:

NUL (ASCII 0), \n, \r, \, ', ", and Control-Z

where \n is the newline character (ASCII x'0A') and \r is the "carriage return" character (ASCII x'0D') .

mysqli_set_charset($link, 'utf8');
$s = mysqli_real_escape_string($link, chr(0) . "\n\r\\'" . '"' . chr(26));
var_dump($s);

Prints:

string(14) "\0\n\r\\\'\"\Z"

So:

  1. Ensure your sever connection use character set uses utf8mb4 or utf8.
  2. Either escape those 7 special characters as shown above or don't allow some or all of them in your passwords.
  3. Ensure that you have single quotes around your escaped password.

So, for example, if you only allow alphanumerics [A-Za-z] plus special characters from '@#$%&!_', then no escaping would be required. If you additionally allowed single and or double quotes, then you just have to make sure that these are escaped with a backslash.

Upvotes: 1

Related Questions