SQL Injection within a stored procedure?

Question

I've found somewhere in the code where a string isn't being escaped properly. I've been trying to see if it is exploitable (don't worry, I'll end up escaping it or using prepared statements anyway, this is just for a learning experience).

This is using mysqli->query() function;

The Query is generated in PHP like so:

$Query = "CALL some_proc(".$_SomeID.",'".$_UnescapedString."')";

By inputting $_UnescapedString as test'); DROP TABLE SomeTable; -- I got the query:

CALL some_proc(1, 'test'); DROP TABLE SomeTable; -- ')

This query was successfully run but it seems that it didn't run the second query. I tested this by putting invalid SQL in the second query and got no errors. I assume this means mysqli is smart enough to only execute a single query?

Now my question is, can I somehow inject SQL into the stored procedure itself? Here is the procedure:

BEGIN
   SELECT COUNT(*) AS SomeCount
   FROM DataTable
   WHERE DataTable.SomeID = _SomeID
   AND DataTable.SomeValue LIKE CONCAT('%',_UnescapedString,'%');
END

I've tried various SQL such as test','%')-- to see if the query would carry on as normal, but it only changes the stored procedure call, i.e:

CALL some_proc(1, 'test', '%')--');

Is there anyway to get a DROP TABLE command into _UnescapedString?

Ɖiamond ǤeezeƦ · Accepted Answer · 2013-05-09 10:43:05Z

1

Disclaimer, I use SQL Server and not mySQL, but assuming the behavior with regards to parameters in stored procedures is the same, and also assuming that _UnescapedString is an input parameter, putting DROP TABLE in the parameter would look like this:

SELECT COUNT(*) AS SomeValue
FROM DataTable
WHERE DataTable.SomeID = _SomeID
AND DataTable.SomeValue LIKE '%DROP TABLE%');

With regards to the query:

CALL some_proc(1, 'test'); DROP TABLE SomeTable; -- ')

Maybe the DROP TABLE command did not execute due to the user account under which you are running having insufficient permissions to execute DDL statements?

Restricting the permissions of the user account being used to access the database from the web server is a way to limit the damage that an SQL Injection attack could cause. However, it won't stop them.

answered May 9, 2013 at 10:43

Ɖiamond ǤeezeƦ

3,3413 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatthewMcGovern Over a year ago

I was wondering if the %DROP TABLE% part could somehow be used to close the CONCAT prematurely and then run the DROP command afterwards? The account I'm using has full access, after looking online I believe it's because mysqli->query() only allows a single query for security reasons.

Ɖiamond ǤeezeƦ Over a year ago

From my experience, there are no inputs via parameters that could close the CONCAT prematurely.

Collectives™ on Stack Overflow

SQL Injection within a stored procedure?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related