0

i am trying to use stored procedures to insert data into a sql database in php. I cant seem to get the data to enter into the database. i am new to php and stored procedures so dont know too much about them.

I have a function called addemployee() where the stored procedure is being used

function addemployee($firstname, $lastname,$email,$username,$dateofbirth,$ppsn,$password, $admin)
{
$mysqli = new mysqli('localhost', 'root', '');

$mysqli->query("CALL prc_emp_add(".$firstname.",".$lastname." , ".$email.", ".$username.", ".$dateofbirth.", ".$ppsn.", ".$password.", $admin)");
echo 'GOT HERE';
//mysqli_query($db, $query) or die(mysqli_error($db));
}

Then in another file i am calling that function and passing through variables

addemployee($_POST['firstname'],$_POST['lastname'],$_POST['email'],$_POST['username'],$_POST['dateofbirth'],$_POST['ppsn'],$_POST['password'],$admin);

when i do a var_dump on my data in the function is has gotten passed through so i know the data is there and also the echo statement is working. Really need to know am i executing the call procedure correctly or what is going wrong

Darby_321
  • 27
  • 9
  • where did you select your database? – MH2K9 Nov 24 '14 at 16:00
  • 1
    Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement). You will want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Add error checking (you may be able to see something in the error logs) to your `mysqli_*` calls. – Jay Blanchard Nov 24 '14 at 16:00
  • **WARNING**: When using `mysqli` you should be using parameterized queries and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation to accomplish this because you will create severe [SQL injection bugs](http://bobby-tables.com/). – tadman Nov 24 '14 at 16:27

1 Answers1

0

Your SQL is invalid. None of your stored procedure values are quoted, so you're producing something that looks like

CALL prc_emp_add(John, Doe, jdoe@example.com, ...)

This also means you are vulnerable to sql injection attacks.

The quick/immediate fix is to quote the values:

$mysqli->query("CALL prc_emp_add('".$firstname."','".$lastname." etc...
                                 ^--------------^-^---etc...

But that still leaves the underlying injection problem open.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Quick fixes like this just create more problems. Switching to placeholders would make this work properly rather than by chance. – tadman Nov 24 '14 at 16:27
  • i have tried it this way but still no luck, i dont need to worry about sql injections as this is just for a college project and we arent looking at security for it – Darby_321 Nov 24 '14 at 20:21
  • then treat this as a learning experience: Your code is simply ASSUMING nothing could ever go wrong. That's exactly the WRONG attitude. Always assume failure, check for that failure, and treat success as a pleasant surprise: `$mysqli->query(...) or die($mysqli->error)`. have the system TELL you what went wrong. – Marc B Nov 24 '14 at 20:23
  • Got it working with the advice you gave me above, must of had some little error in another part of it, thanks. That has been at me the last two days – Darby_321 Nov 24 '14 at 20:43