call stored procedure to insert data iwith php function but not submitting form data

[UnstoppableMadman]

Hello guys i have been learning how to call a stored procedure with php function, when i create the object and call the stored function using mysqli_query function, it seems the php function is executed successfully, but no data is inserted into the database. the stored procedure in mysql works just fine, as well as the connection the database is fine....

 

I have pasted my codes for your help can someone tell what could be wrong with the codes: please note idecided to extablish the network within the php function the perform the calling of the procdure afterwards.

 

<?php

 

class agmInfo {

public function addAgm($agm_name, $agm_year, $held_location, $attendedshareholders, $nature, $asili, $description, $sw_description, $resolutions, $sw_resolution, $visibility)
{
$server='localhost';
$user= 'root';
$password = 'karavina';
$database ='NIC';

$connection = mysqli_connect($server, $user, $password, $database);

// Check connection

if($connection === false){

    die("ERROR: Could not connect. " . mysqli_connect_error());

}

else { return $connection;}
$query = "CALL addAgm(".$agm_name.", ". $agm_year.", ". $held_location.", ". $attendedshareholders.", ". $nature.", ". $asili.", ". $description.", ". $sw_description.", ". $resolutions.", ". $sw_resolution.", ". $visibility.")";
$sql = mysqli_query($connection, $query);

        if($sql) {
            echo "<div class='alert alert-success'>
                  <strong>Success!</strong> The Annual General meeting details for year ".$agm_year." held at ".$held_location." has been submitted.</div>";
                    }
                else {
                    echo "<div class='alert alert-danger'>".
                          "<strong>Failure!</strong> The Annual General meeting details failed to be submitted, please <strong>check the details you have provided carefully, then try again.</strong> <br>If the problem persists please contact technical support or system Administrator.".
                        "</div>";
                    }

 

 

}// end of function

}// end of class

$agmnow = new agmInfo();
$agmnow->addAgm($_POST['agm_name'], $_POST['held_location'], $_POST['agmyear'], $_POST['attendedshareholders'], $_POST['nature'],

$_POST['asili'], $_POST['description'], $_POST['sw_description'], $_POST['resolutions'], $_POST['sw_resolution'], $_POST['description']);
 

?>

 

PLEASE I NEED YOU HELP....

[mac_gyver]

your code contains an else { return $connection; } statement as part of the connection logic, so, none of the code past that point is being executed.

your main code should be responsible for making the database connection. you should use dependency injection to supply the connection to the  agmInfo class, when you make an instance of that class.

in order to prevent sql injection, you should use a prepared query, with place-holders in the sql query statement for each data value, then supply the actual data when you execute the query.

[UnstoppableMadman]

2 minutes ago, mac_gyver said:

your code contains an else { return $connection; } statement as part of the connection logic, so, none of the code past that point is being executed.

your main code should be responsible for making the database connection. you should use dependency injection to supply the connection to the  agmInfo class, when you make an instance of that class.

in order to prevent sql injection, you should use a prepared query, with place-holders in the sql query statement for each data value, then supply the actual data when you execute the query.

intially i had not extended the if statement to return the $connection variable, but i had done that so i could pass it the mysqli_query function.

 

just for your help so i understand better, i had resoplved to use stored procedures as a measure to escape the sql injection, so prepared statement are better than the stored procedure? just trying to understand better....

 

[ginerjm]

Where is this 'stored procedure'?  All I see is a class definition.

[UnstoppableMadman]

4 minutes ago, ginerjm said:

Where is this 'stored procedure'?  All I see is a class definition.

I have it in the variable $query = "CALL addAgm(".$agm_name.", ". $agm_year.", ". $held_location.", ". $attendedshareholders.", ". $nature.", ". $asili.", ". $description.", ". $sw_description.", ". $resolutions.", ". $sw_resolution.", ". $visibility.")";
$sql = mysqli_query($connection, $query);  inside the function that is in the class definition...

[mac_gyver]

any sql special characters in the data, either accidentally or intentionally, that's being put directly into the sql query statement can break the sql query syntax.

for a stored procedure call, i don't know if you can do anything nefarious by injecting sql, but you can still trigger database errors.

a prepared query is the simplest way of preventing sql special characters from breaking the sql query syntax, regardless of using a stored procedure or not.

[UnstoppableMadman]

1 minute ago, mac_gyver said:

any sql special characters in the data, either accidentally or intentionally, that's being put directly into the sql query statement can break the sql query syntax.

for a stored procedure call, i don't know if you can do anything nefarious by injecting sql, but you can still trigger database errors.

a prepared query is the simplest way of preventing sql special characters from breaking the sql query syntax, regardless of using a stored procedure or not.

thank you for your response. i had study the manual for mysql in depth... stored procedures has got more advantage over prepared statement... aside from preventing the sql injection, it also reduces the traffic better the server and the application when querying the database using the calls of stored procedures, and can easily be extended to other languages in case the need to share the database is there.

 

thats why am learning the call stored procedure... i am used to prepared statements, but i want to make a change, any help on this would be highly appreciated, because it seems the stored procedure calls are not complete, though the function works, no data is entered...  I REALLY NEED help as i have been stuck for a week, despite my googling over the net...

[ginerjm]

Well - I guess I don't know how this is supposed to work since I missed it earlier.

My opinion is that you have been told how to solve your problem.  YOu should follow the very good advice you have been given.  Read up on using prepared queries and

learn how to use them.

 

BTW - Can you explain how your script is supposed to function?  You define a class that contains a method named 'admAgm'.  In your mainline code you create an instance of this class and then you execute that 'admAgm' method.  My confusion is that inside this method you write a query statement that calls(?) a function/method named 'addAgm'.  So either you have another function that exists as your so-called "stored procedure" (which you are not showing us) or your code is recursively calling your class method.

[UnstoppableMadman]

2 minutes ago, ginerjm said:

Well - I guess I don't know how this is supposed to work since I missed it earlier.

My opinion is that you have been told how to solve your problem.  YOu should follow the very good advice you have been given.  Read up on using prepared queries and

learn how to use them.

 

BTW - Can you explain how your script is supposed to function?  You define a class that contains a method named 'admAgm'.  In your mainline code you create an instance of this class and then you execute that 'admAgm' method.  My confusion is that inside this method you write a query statement that calls(?) a function/method named 'addAgm'.  So either you have another function that exists as your so-called "stored procedure" (which you are not showing us) or your code is recursively calling your class method.

i had set the stored procedure in the mysql database already, thats why my query in the function is to call that stored procedure in the database in action, by passing the values i have collected in function parameters, which then get inserted in to the stored procedure to be excuted by the database while inserting the data. me calling the function is passing the values to the stored procedure to the database....

[ginerjm]

Ok - then do we not get to see how this procedure is doing what it can to protect you and your database?

[UnstoppableMadman]

 

6 minutes ago, ginerjm said:

Ok - then do we not get to see how this procedure is doing what it can to protect you and your database?

Below is the SHOW CREATE PROCEDURE statement; this is the stored procedure i call with the method i used the class... the procedure inserts values in terms of variablkes into the procedure's insert statement.

CREATE DEFINER=`root`@`localhost` PROCEDURE `addAgm`(
 `agm_name` varchar(50),
`agm_year` date,
`held_location` varchar(50),
`attendedshareholders` int(10),
`nature` enum('Extra oridinary General Meeting','Ordinary General Meetiong','Emergency Meeting'),
`asili` enum('Mkutano MKuu wa Dharura','MKutano MKuu wa Mwaka','Mkutano wa Dharura'),
`description` text,
`sw_description` text,
`resolutions` text,
`sw_resolution` text,
`visibility` enum('Yes','No')
)
BEGIN
INSERT INTO agm (agm_name, agm_year, held_location, attendedshareholders, nature, asili, description, sw_description, resolutions, sw_resolution, visibility)
VALUES  (@agm_name, @agm_year, @held_location, @attendedshareholders, @nature, @asili, @description, @sw_description, @resolutions, @w_resolution, @visibility);
END

[Barand]

FYI

Stored procedures do the same job of separating the values from the query as prepared statements do. Both involve passing the values as parameters instead of embedding the variables' values directly in the query string.