Join 2 strings as query in a procedure

Question

How can I construct a query based on parameters I receive? I want to add to the end of the query.

I tried something like this but it didn't work:

DELIMITER $$
CREATE PROCEDURE `get_users`(IN sortstring TEXT)
BEGIN
PREPARE statement FROM
 "SELECT username, password FROM users ?";
SET @param = sortstring;
EXECUTE statement USING @param;
END$$

And I would pass to sortstring something like:

ORDER BY username DESC

Can I do this simpler by using concat or something?

Gotta run, but look here: http://forums.mysql.com/read.php?60,41372,47813 — MatBailie, Oct 14 '11 at 16:23
@Dems Thank you. Can you make a post so i can mark it as the correct answer. — monokh, Oct 14 '11 at 18:26
Make sure you know what "SQL injection" is when using code like this. — Jamie F, Oct 14 '11 at 20:10

score 2 · Accepted Answer · edited May 23 '17 at 12:19

You need to deconstruct the sortstring and check all its parts against a whitelist of allowed terms.

See the following pseudo code. I haven't fully tested it, but lets say it's the idea that counts.

DELIMITER $$

CREATE PROCEDURE `get_users`(IN sortstring TEXT)
BEGIN
  //check sortstring against a whitelist of allowed sortstrings
  DECLARE sortpart VARCHAR(255);
  DECLARE done BOOLEAN DEFAULT false;
  DECLARE allok BOOLEAN DEFAULT true; 
  DECLARE i INTEGER DEFAULT 1;

  WHILE ((NOT done) AND allOK) DO
    SET sortpart = SUBSTRING_INDEX(sortstring,',',i);
    SET i = i + 1;
    SET done = (sortpart IS NULL);
    IF NOT DONE THEN
      SELECT 1 INTO allok WHERE EXISTS 
        (SELECT 1 FROM whitelist 
         WHERE allowed_sort_claused = sortpart AND tablename = 'users');
    END IF
  END WHILE;
  IF allOK THEN     
    PREPARE statement FROM 
      CONCAT('SELECT username, passhashwithsalt FROM users ',sortstring);
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
  ELSE SELECT 'error' as username, 'error' as passhashwithsalt;
  END IF; 

END$$

See: How to prevent SQL injection with dynamic tablenames?

The error in your code
You cannot use columnnames or SQL-keywords as parameters. You can only use values as parameters. For that reason your query will never pass prepare.
The ? in SELECT x FROM t1 ? wil just be replaced by SELECT x FROM t1 'ORDER BY field1, field2' Which makes no sense.

Join 2 strings as query in a procedure

1 Answers1