Help - creating stored proc using PDO statement

[SLSCoder]

I'm trying to create a stored procedure with multiple statements using a PDO statement.
If I run the sql in Navicat it works fine.
If I run it in PHP using a PDO statement it does not throw any errors and the execute returns true but no stored procedure is created.
I have this setting for PDO: 

$options = [
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,            
            PDO::ATTR_EMULATE_PREPARES   => 1,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
            PDO::NULL_TO_STRING  => 1
        ];

Please let me know what I'm doing wrong.

The SQL:
 

DROP PROCEDURE IF EXISTS test1; 
DELIMITER || 
CREATE PROCEDURE test1(IN LastID INT(5)) 
BEGIN 
SELECT person.RecordID AS PersonID, organization.Name FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 10;
SELECT person.RecordID AS PersonID, organization.Name, organization.City, organization.State, organization.Zip FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 25; 
END || 
DELIMITER ;

The PHP code that generates the SQL:
 

$oDaSrv = new daSurvey();
$sql = " DROP PROCEDURE IF EXISTS test1;
DELIMITER ||
CREATE PROCEDURE test1(IN LastID INT(5))
        BEGIN
            SELECT person.RecordID AS PersonID, organization.Name FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 10;
            SELECT person.RecordID AS PersonID, organization.Name, organization.City, organization.State, organization.Zip FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 25;
        END || 
DELIMITER ;";
//echo "<pre>" . $sql . "</pre>";

try {
    $ppd = $oDaSrv->getPPDObj($sql);
    //$ppd->bindValue(":QuestionBankID", "10");
    $bDone = $ppd->execute();
    if(!$bDone) {
        $arErr = $ppd->errorInfo();
        $sErr = "";
        if(is_array($arErr)) {
            for($i=0; $i < count($arErr); $i++) {
                $sErr .= $arErr[$i] . "<br>";
            }
            echo "PDO Error:" . $sErr;
        }
        else {
            echo "PDO Error: NONE";
        }
    }
}
catch(Exception $exp) {
   echo "ERROR: " . $exp->getMessage();
}

 

[requinix]

Any particular reason you want to create stored procedures programmatically? Those are normally things you set up ahead of time, not while the application is running.

[SLSCoder]

requinix: I keep seeing that. The reason is that the queries are built programmatically based on user selections.
Did you notice that I'm using PDO, which is where the problem lies? The reason is that I need to prevent sql injection from the user inputs.

Is there no way to programmatically create stored procedures with multiple statements using PDO?

[Barand]

All I'm seeing in your posts is...

This is what I'm doing

Can you help?

[Barand]

When it comes to SQL injection, stored procedures and prepared statements both do the same thing - they separate the data values fron the query statement. The mechanisms are slightly different, however.

• With prepared statements, placeholders are used instead of the data, and the values are passed at execution time.
• With stored procedured, the values are passed as input parameters when the procedure is called.

Using both is a "belt and braces (sorry, suspenders)" approach.

The advantage of a sproc is it is pre-written and stored (clue is in the name) waiting to be called so there is no overhead of sending the query statement - you just send the data. If you use "dynamic sprocs" you throw away this advantage. To me the idea of a "dynamic sproc" is oxymoronic, like military intelligence, internet privacy, common sense.

[requinix]

Exactly. And in here,

DROP PROCEDURE IF EXISTS test1; 
DELIMITER || 
CREATE PROCEDURE test1(IN LastID INT(5)) 
BEGIN 
SELECT person.RecordID AS PersonID, organization.Name FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 10;
SELECT person.RecordID AS PersonID, organization.Name, organization.City, organization.State, organization.Zip FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 25; 
END || 
DELIMITER ;

there's nothing about it that means it can't be set up ahead of time. You create this stored procedure yourself in the database, preferably under a different name, and then you can use PDO to invoke it.
The LastID variable is obviously not known until code wants to call the procedure, but the procedure itself doesn't vary.

[Barand]

Why the two queries when the first set of results is just a subset of the second?

[SLSCoder]

I appreciate your responses but you guys are killing me.

The name of the sproc is TEST! The actual stored procedure could be lots (hundreds) of different variations depending on user inputs.
Organizations (aliased as: branches, clients, vendors, gov. agencies, associations, etc.) people (aliased as: branch employees, clients, client employees, vendors, vendor employees, team members, surveyors, government agents, association members, etc . . . )
Other potential (user created) tables such as departments, products, product types, projects, project types, jobs, job titles (attached to aliased people), divisions (linked to organizations), teams (linked to people), surveys (with different types of questions - and answers), billing information  . . .
I'm trying to illustrate how easily a database can get complicated, making reporting difficult. The users can create tables and select from a myriad of relational tables for reporting.
I need the input field values ONCE to create the query. When it is called (usually many times) the only things that will change are the Last ID and the LIMIT (for paging).

The question I asked was very basic. I'm simply trying to learn how to create a multiple statement stored procedure using PDO.
The values in the user inputs will be required only once to create the query(s).
I do not want the user input values to be parameters in a prepared statement if at all possible.
I'm trying to get them past sql injection and store them as values (not parameters) in the prepared queries.

The only thing that will change is the Last ID and the LIMIT (so you see, the query(s) does change every time it's called).
These would be the parameters.
The stored procedure will potentially be called many times over the course of months, even years.

I'm having a hard time believing that I'm the only one in the world trying to do this.
It looks to me like PDO doesn't work correctly. I get no errors and execute returns true but it doesn't create the sproc.

I asked a simple question: Is there any way to programmatically create a stored procedure having multiple statements in PHP using PDO for a MySql database?
More directly: Is there any way to programmatically create a stored procedure (or callable prepared statement) and protect all user inputs from sql injection and make them values (not parameters) in the prepared statement?

If you can't help me I understand and I appreciate your input.

[Barand]

In the 1980's there was a PC program generation application called "The Last One" which was marketed as "No one will ever need to write a program again". Needless to say, it failed miserably.

I get the impression you are trying to create another "Last One". No one will ever need to write another query again.

1 hour ago, SLSCoder said:

The users can create tables

Seriously!?

Edited September 29, 2021 by Barand

[SLSCoder]

Uncle. I'll figure it out. Thanks

[benanamen]

You will get a lot farther if you give us a high level overview of what you have going on rather than how you want to do it. "The users can create tables" is a very bad smell. Something is not right. 

What we currently have here is know as an XY Problem.
https://xyproblem.info/

[SLSCoder]

Have none of you ever developed a report builder? I'm trying to develop a report builder.
Are you all adverse to users creating tables? Most of the larger APIs that I use have the ability to create tables and add fields.
Why is that such a big problem?

This does not match the description for your xy problem.
I know exactly what I'm trying to do and gave you a very specific example of it.
I asked a very specific question about PDO.
I tried to create a MySql stored procedure having multiple sql statements using PDO to prevent sql injection.
It's execute returns true and does not throw an error but will not create the stored procedure.

The problem that I'm having is very specific.
I want to create a stored procedure that has multiple sql statements programmatically.
The sql statements are derived from user input. I want to prevent sql injection from the user input.

I gave you a specific example of what I'm trying to do.

NONE of you have even attempted to answer my question.

[benanamen]

Since you are insisting on doing things they way you are, here is working code.
 

<?php

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$sql = 'DROP PROCEDURE IF EXISTS test1';
$pdo->exec($sql);

$sql ='CREATE PROCEDURE test1(IN LastID INT(5))
BEGIN
SELECT person.RecordID AS PersonID, organization.Name FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 10;
SELECT person.RecordID AS PersonID, organization.Name, organization.City, organization.State, organization.Zip FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 25;
END';

$pdo->exec($sql);

 

[SLSCoder]

benanamen: Thank you. I still didn't make something clear and your code isn't working for me.
If I didn't have to deal with client side form input values I would just use mysqli.

I remmed out a client side variable because this was just a test.
I'm using a PDO statement so that I can bind client side inputs to it.
You used PDO exec instead because the queries in the test didn't include param labels (client side form inputs)
My bad - sorry.

I noticed that you ran the code to drop the sproc in a seperate query.
I did that as well and didn't have any problem with it (I used mysqli though).
I also noticed you changed the PDO option: PDO::ATTR_EMULATE_PREPARES => false,
I had that set to 1 per this stackoverflow thread: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd
I quote him "Make sure PDO::ATTR_EMULATE_PREPARES is set to 1". So that person disagrees with you.
I set that option to false after reading your post above. I tried it both ways - no effect.

I noticed that you removed the delimiter change before CREATE PROCEDURE.
How is that going to work? If I understand correctly as soon as MySql sees the ; it will stop.
I tried it anyway. Without the delimiter change PDO did create the sproc but with no sql in it at all.

When I run the code you provided but include the delimiter change and use a PDO statement it throws a syntax error.
If I run the same code in Navicat it works fine so there's nothing wrong with the sql, it's an issue with PDO.

Fatal error: Uncaught Exception: ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER || CREATE PROCEDURE test1(IN LastID INT(5)) BEGIN SELECT person.R' at line 1

 

OK I changed my code a bit to include a variable and a PDO label. Please don't say I don't need the variable. It is a simulated client side form value. This is a test.
 

$ClientVal = "San Diego";
$oDaSrv = new daSurvey();
$sql = " DROP PROCEDURE IF EXISTS test1;";
$oDaSrv->executeNonQuery($sql);
$sql = "DELIMITER || 
		CREATE PROCEDURE test1(IN LastID INT(5))
		BEGIN
			SELECT person.RecordID AS PersonID, organization.Name FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID LIMIT 10;
			SELECT person.RecordID AS PersonID, organization.Name, organization.City, organization.State, organization.Zip FROM person INNER JOIN organization ON person.lnk_organization = organization.RecordID
			WHERE organization.City = :OrgCity LIMIT 25;
		END ||
		DELIMITER ;";
//echo "<pre>" . $sql . "</pre>";

try {
	$ppd = $oDaSrv->getPPDObj($sql);
	$ppd->bindValue(":OrgCity", $ClientVal);
	$bDone = $ppd->execute();
	if(!$bDone) {
		$arErr = $ppd->errorInfo();
		$sErr = "";
		if(is_array($arErr)) {
			for($i=0; $i < count($arErr); $i++) {
				$sErr .= $arErr[$i] . "<br>";
			}
			echo "PDO Error:" . $sErr;
		}
		else {
			echo "PDO Error: NONE";
		}
	}
}
catch(Exception $exp) {
	echo $exp->getMessage();
}

When I run this code it throws an error.
I don't think it likes the delimiter change. I tried adding a ; after that line but it didn't change anything.
I don't see how to do this without the delimiter change. Again, when I deleted it PDO did create the sproc but there was no sql in it.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER || CREATE PROCEDURE test1(IN LastID INT(5)) BEGIN SELECT perso' at line 1

 

[benanamen]

After re-reading this thread it seems apparent that what you really need is a BI (Business Intelligence) tool that is purpose built for the real problem you are trying to solve, generating various reports. Tableau and Power BI are such tools. (There are many others).

I am going to bow out of your specific request since I think you are going about it wrong and are not willing to do anything beyond "how" you are trying to solve a problem.

[SLSCoder]

If the people who wrote the Business Intelligence software could do it then why can't I?
In fact, I am developing a tool much like theirs but for a slightly different purpose and probably not *quite* as complex.

Thanks for your response.

[gizmola]

You can not change the delimiter.  It is not possible because changing the delimiter is not a serverside command -- it is a clientside instruction that the mysql command line client understands.  

As explained and illustrated previously in this thread, you do not need to change the delimiter because that is only a problem for the mysql client, as it uses the ";" to send a query to the server.  Your PDO code can include semicolons.

In summary, you are wasting your time trying to run sql that changes the delimiter.  As to whether or not you can run code that creates sprocs with bound parameters or values, I suspect that you can, as you certainly can bind parameters to a "CALL sproc" statement.  With that said, I have never tried to do so.