How to use MySQLi Prepared Statements with Stored Procedures

Question

I'm trying to learn more about MySQL and how to protect against SQL injections so my research has brought me to Prepared Statements which seems to be the way to go.

I'm also working on learning how to write Stored Procedures and am now trying to combine the two. There isn't much info on this though.

At the moment in my PHP test app I have a function that calls an SP with a normal MySQL command like this:

mysql_query("CALL usp_inserturl('$longurl', '$short_url', '$source')");

How can I do the same with MySQLi and a Prepared Statement to make it as safe as possible to injections?

Thanks!

Answer 1

You might find the following answer of use:

MySql: Will using Prepared statements to call a stored procedure be any faster with .NET/Connector?

In addition:

GRANT execute permissions only so your application level user(s) can only CALL stored procedures. This way, your application user(s) can only interact with the database through your stored procedure API, they can not directly:

select, insert, delete, update, truncate, drop, describe, show etc. 

Doesn't get much safer than that. The only exception to this is if you've used dynamic sql in your stored procedures which I would avoid at all costs - or at least be aware of the dangers if you do so.

When building a database e.g. foo_db, I usually create two users. The first foo_dbo (database owner) is the user that owns the database and is granted full permissions (ALL) so they can create schema objects and manipulate data as they want. The second user foo_usr (application user) is only granted execute permisisons and is used from within my application code to access the database through the stored procedure API I have created.

grant all on foo_db.* to foo_dbo@localhost identified by 'pass';

grant execute on foo_db.* to foo_usr@localhost identified by 'pass';

Lastly you can improve your code example above by using mysql_real_escape_string:

• http://php.net/manual/en/function.mysql-real-escape-string.php

   $sqlCmd = sprintf("call usp_inserturl('%s','%s','%s')", 
    mysql_real_escape_string($longurl),  
    mysql_real_escape_string($shorturl), 
    mysql_real_escape_string($source));
  
   $result = mysql_query($sqlCmd);