Escape string on server side

Question

I have a stored procedure in a database that accepts string arguments that are inserted directly into a query. I have client-side code to escape the inputs, but that doesn't stop anyone with permission to execute that procedure with bad arguments and inject SQL.

Current implementation is something like this:

CREATE PROCEDURE grantPermissionSuffix (perm VARCHAR(30), target VARCHAR(30), id VARCHAR(8), host VARCHAR(45), suffix VARCHAR(45))
BEGIN
  SET @setPermissionCmd = CONCAT('GRANT ', perm, ' ON ', target, ' TO ''', id, '''@''', host, ''' ', suffix, ';');
  PREPARE setPermissionStmt FROM @setPermissionCmd;
  EXECUTE setPermissionStmt;
  DEALLOCATE PREPARE setPermissionStmt;
  FLUSH PRIVILEGES;
END

Clearly this is a recipe for disaster. How can I prevent an injection opportunity? Is there a standard SQL function to escape input? Is there one for MySQL? Is there another way to get the same result without extra client-side code?

My fear is that the only solution will be client-side prepared statements, which is not an option at this time. I need all the logic to be handled on a server, requiring clients to only call this procedure (I don't want to have to grant users permission to modify tables/permissions directly, only to handle it with procedures they're allowed to execute).

Answer 1

You can prepare statements with ? placeholders for variables and later EXECUTE USING the variables, just like in client-side prepared statements, at least according to the manual. I'm not sure how well this would work when substituting table names, though, but this is limited by prepare rules, so if it doesn't work in server-side, it wouldn't work on client-side either.

UPDATE:

Apparently, mysql doesn't recognize ? placeholders in prepared GRANT query. In that case, you'll have to take care of it manually. Some tips are in that answer - namely, using ` (backtick) to escape identifiers and using a whitelist for keywords - that way, you also gain fine-grain control on what you do allow in your procedure.

I would add that for your specific purposes it might be better to select from information_schema and mysql tables to control that, for example, db and table passed to you actually exist. You can use prepared statements with placeholders for that, so it's safe. Something like this will check db and table:

PREPARE mystat FROM 'SELECT count(*) into @res FROM INFORMATION_SCHEMA.TABLES WHERE upper(TABLE_SCHEMA)=UPPER(?) and UPPER(TABLE_NAME)=UPPER(?)';

set @db = 'mydb'; --these two are params to your procedure
set @table = 'mytable';
set @res = 0;
execute mystat using @db, @table;
select @res; --if it's still 0, then no db/table exists, possibly an attack is happening.

Checking user and host can be done like that, too, using mysql.users table instead. For the rest of params, you'll have to build a whitelist.

Yet another way I see is to check for allowed characters using a regular expression - there's REGEXP command for that. For example, you can control that your procedure parameter has only alphabetic uppercase with if @var REGEXP '^[A-z]+$'. To my knowledge, it's impossible to perform an SQL injection using only A-z.