1

In MySQL is a prepared statement inside a stored procedure safe from SQL injection? See example below. The get_info stored procedure is passed a table name (pTbl) and the where clause (pWhere). pWhere can have many AND's (e.g. fld1="a" AND fld2="b" AND ...). It's probably not the best way to do it but I need to have dynamic sql.

CREATE PROCEDURE get_info(pTbl VARCHAR(10), pWhere TEXT)
BEGIN
    SET @uSQL = CONCAT('SELECT info FROM ',pTbl,' WHERE ',pWhere);
    PREPARE ps FROM @uSQL;
    EXECUTE ps;
END$$

I tried calling the stored procedure like below using MySQL Query Browser but only got an error back saying I have a syntax error in my SQL.

CALL get_info('tbl','1=1;SELECT * FROM information_schema.TABLES;');

If it helps any the stored procedure is being called from PHP using PDO like below. $tbl is a $_SESSION variable and $whr is a $_GET variable.

$s=$c->prepare("CALL get_info(?,?)");
$s->execute(array($tbl,$whr));

Is this stored procedure safe? If not, how would I inject it? Does it make a difference if I inject from MySQL Query Browser vs from a web page? Thanks...

Mat
  • 202,337
  • 40
  • 393
  • 406
Txoov
  • 174
  • 1
  • 2
  • 10
  • The question is incomplete; you fail to mention where `$tbl` and `$whr` come from. – Ignacio Vazquez-Abrams Aug 28 '11 at 10:28
  • @Ignacio Vazquez-Abrams, $tbl is a $_SESSION variable and $whr is a $_GET variable. – Txoov Aug 28 '11 at 10:34
  • Why use a stored procedure for this, rather than generating the statement in PHP? You can build the [`WHERE` clause dynamically](http://stackoverflow.com/questions/327274/mysql-prepared-statements-with-a-variable-size-variable-list) and still use parameters for user input, thus preventing injection. – outis Aug 28 '11 at 11:10
  • @outis, I have other stored procs constructed in a similar fashion that are more complex. They create temp tables, have multiple sql statements. I can probably move them out to PHP but for me it's cleaner to manage stored procs than PHP scripts. Also I want to limit the back and forth between PHP and the database. – Txoov Aug 28 '11 at 17:21
  • @Txoov: if you were to parameterize the prepared statements in the other procedures, would they be variadic? That's the one thing that's tricky to deal with in SQL but not PHP. If the others are invariadic (constiadic?), they can be made safe simply with parameters. `get_info`, however, is more difficult if not impossible to make safe from within SQL. You'll have to perform at least some sanitizing in PHP. – outis Aug 28 '11 at 19:35
  • @outis, thanks for your input. After playing around today and testing for myself I am converting all my stored procedures to PHP. – Txoov Aug 29 '11 at 04:05

3 Answers3

2

Yes, it is safe. (edit: no, it's not)

The crucial point is to know when is the SQL text analyzed and transformed into the semantic tree. Prepared statements are precisely that: statements that are prepared, just waiting for the arguments. They're stored in the server fully compiled to the internal execution plan, with the 'holes' for the missing arguments.

That's why you're getting the syntax error, you're trying to set the whole WHERE part as an argument; but it's a whole expression tree. The prepared statement can only have 'holes' for data elements, not for syntactic text.

The protocol that transfers the arguments is fully binary safe, no matter what you have in your argument variables, they'll be send as binary data and used only as data, not as part of the SQL command.

edit ooops! i've just noticed that you are doing text interpolation, just not in PHP but in SQL. That means you're constructing the SQL command later, using external data.

definitely unsafe.

Javier
  • 60,510
  • 8
  • 78
  • 126
1

Since one of the values comes from the user, some forms of SQL injection are possible; while only SELECT queries can be run, it is still possible to reveal information by passing 1=1 to the page. The actual usefulness of the information revealed in this manner may be low, but it can still happen.

Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
1

Any time you interpolate a value into a statement there's the possibility of injection. The procedure is vulnerable. The only general limitation with injection in SQL procedures and functions is that PREPARE works on a single statement. In this specific case, that the injected text is after a WHERE clause in a SELECT basically limits attacks to sub-selects, UNION, calling procedures and functions (tricky but potentially very dangerous), and dumping to files (if the definer of get_info has the FILE privilege).

As an example, try:

CALL get_info('tbl','1=0 UNION SELECT CONCAT(user, "@", host, " ", password) FROM mysql.user;');
outis
  • 75,655
  • 22
  • 151
  • 221