SLSCoder

I have a table1 with an ID and a Name where the ID is found in a sql query on different table2. I have access to an existing PHP array containing all the IDs and Names of table1. For each of the records I find n the sql query on table2 I have a choice. I can either INNER JOIN table1 on table2 in the sql query and then include the Name in the query OR while looping through the query recordset, for each record found I can loop through the array of table1 and match the ID to the ID in the query record to get the Name. Which method is generally faster?

I added the code in the page because it still would not display the errors. I wasn't confident that I had the 'right' php.ini file even though I did edit the one shown in php_info.php. Normally, that code wouldn't be there. I'm aware that & ~E_NOTICE turns off notices. My problem was that even if I have php.ini set as: error_reporting = E_ALL the page still wouldn't show the parse error. The error was caused by a missing semi colon and yesterday the page returned: This page isn’t working Today the error displays as it should. I've removed the error display code on the page and it still works fine. I *swear* I didn't change anything. Yesterday when I quit, it didn't work. Today, it works just fine. ?#)!@#$??? It's all good now. You guys are a lot of help. We appreciate you. Thanks

This code works $sErr = "Hello World"; echo $sErr . "<br>"; This code makes the page display: This page isn’t working $sErr = "Hello World" echo $sErr . "<br>"; Why is it doing that and how can I make it show me the error?

Yea, sorry. I'm working on it. It turns out that it seems to be doing it on compile time errors. What caused it to return This page isn't working was a missing semi-colon.

I've been through this a hundred times. I cannot make php display errors consistently. My php.ini file has this: error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED & ~E_STRICT display_errors = On display_startup_errors = On log_errors = On ignore_repeated_errors = Off ignore_repeated_source = Off report_memleaks = On Nothing exists below these params that would change them. I have a file https://dev.aecperformance.com/php_info.php which shows my php info. When I pull that up I see: Loaded Configuration File /etc/php/8.1/apache2/php.ini I don't understand why this is. It used to be in /etc/php/8.1/fpm php8.1-fpm is installed and is active. Whether or not php is actually using it I can't say. It should be using fpm. Nevertheless, I have uploaded the php.ini file to both: /etc/php/8.1/apache2/php.ini and to /etc/php/8.1/fpm/php.ini I've rebooted the server since making the changes to make sure that php is reloaded. On my web page I have: ini_set('display_errors', 1); ini_set('display_startup_errors', 1); error_reporting(E_ALL); STILL when I try to load the page I get: This page isn’t working https://dev.aecperformance.com/test.php How can I make PHP display errors and suppress warnings?

OK I get it. thanks

I figured this out. The problem was not with the at command, the user www-data did not have permissions to create the directory. This is resolved.

I should add that: The file /etc/at.deny does not include www-data (which is $_Server["user"]) The file /etc/at.allow does include www-data.

Ubuntu 20.04 apache2 php 8.0.14 php-fpm I'm trying to execute an at command from php. The code is: $out = shell_exec('echo mkdir /var/www/test | at now +1 minutes'); echo $out . "<br>"; If I run that command from the command line it works fine. When I run it from php I get nothing back into the $out variable and no directory is created. How can I run an at command from php?

I changed the php code to: echo $_SERVER['USER'] . "<br>"; The response this time was: www-data So now I'm confused. Is the logged in user root or www-data? I need the logged in user to be www-data, not root.

ubuntu 20.04 php 8.0 I'm using php-fpm to run php from apache2 The file: /etc/php/8.0/fpm/pool.d/www.conf shows: user = www-data group = www-data The file: /etc/apache2/envvars shows: export APACHE_RUN_USER=www-data export APACHE_RUN_GROUP=www-data The file: /etc/apache2/apache2.conf shows: # These need to be set in /etc/apache2/envvars User ${APACHE_RUN_USER} Group ${APACHE_RUN_GROUP} When I run a php file with this code in it: echo get_current_user() . "<br>"; The response I get is: root Why is the logged in user root instead of www-data and how can I reset it so that the logged in user is www-data? Any help would be greatly appreciated.

gizmola: Thank you for that response. I appreciate your information about MySql. I used to develop in VB ASP and then C#.NET using DBase then MS Sql Server so I guess I expected MySql to be more like Sql Server. The PHP code that creates these queries is over 1,000 lines. It's not so much that the 'query' would be that big, it's just versatile and the code has to cover every possibility. My reason for wanting to use sprocs in part was so that I don't have to run all that PHP code every time the query is used. Also, it does require more than 1 sql statement and I was hoping to do it all in one db communication. The queries are derived from user input. My intention was to create the query structure once and then from there all I'd have to do would be call it. That would speed the PHP code and I thought, also the MySql code. I've learned that I cannot create/store queries from client (I mean the browser) form inputs. From what I understand the only way I can prevent sql injection is with prepared statements (PDO or mysqli). That means I cannot *save* the queries and then reuse them. I have to rebuild them every time I use them. I have to store the parameters/values and then retrieve them every time I use the queries. That will *definitely* slow the app down. I expected to be able to save the queries (multiple sql statements) to something compiled & optimized by MySql and then be able to just use it repeatedly. I don't really care about the fact that sproc memory is allocated per connection. Generally, one person creates a report and that will be the only person who uses it. They will however use it over and over and over, sometimes for years. I appreciate that the people here do this for free. I've depended on forums like this to help me for as long as they have existed on the Internet. I've also contributed to them myself quite a bit. I *am* open to suggestions as to how to accomplish my goals. The only 'answers' I've gotten here involve prepared statements which prevent me from being able to *save* the queries. That means I have to retrieve the parameter/value pairs and then rebuild the queries in PHP every time I use them. Also there is *some* procedural code I would have liked to run in MySql. Thank you all for your help.

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.

The correct answer to this question is that it cannot be done. That is, there is no way using PDO or mysqli prepared statements to create stored procedures from client form inputs as parameters and therefore no way to prevent sql injection. The reason is that prepared statement parameters (PDO or mysqli) cannot be saved as part of a query. The parameters the database, not as part of the sql. The PHP code to create the prepared statement and if not still cached the MySql work to optimize the prepared statement must be executed every time the prepared statement is used. If the prepared statement is to be run repeatedly the parameters must be stored initially and then retrieved every time the prepared statement is called. I think it would be worthwhile to find better ways than prepared statements to prevent sql injection.

requinix: Thanks for your response but I can't get you to read my post. The code is a TEST. I am trying to programmatically create a stored procedure that includes values that are derived from client side form fields and prevent sql injection. My purpose for an sproc is so that my app can call it repeatedly for years after it is created. It is a report created by the user, in the report builder which is what I'm trying to develop. I don't understand why this has to be so complicated and I can't understand why you have so much of a problem just answering my question: How can I programmatically create a stored procedure that includes values that are derived from client side form fields and prevent sql injection?

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

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.

Thanks. The querys would a) require multiple sql statements and b) be called repeatedly for months and even years. gizmola: it is InnoDB. quote: "Rarely do you see these features built with sprocs." Why not? They could call the sproc over and over with 2 params, LastID and Limit. So far, I have heard a whole lot about why I shouldn't create stored procedures with PDO (or at all) but nothing about how to do it. Simple EXAMPLE: the user wants a report on a summary of the answers to questions where the rated entities (not the surveyors) are: Project Managers (job_types:21) of vendors(organizations) that have conducted projects where the project type (project_types:17) is Health Care. This would be a classic report for a user to monitor the performance of vendors' project managers doing health care; in my opinion, not too complicated. The USER determines what they want the report to show them via user inputs in the report builder that I'm trying to develop. I want to create a query that can be called repeatedly for years that matches the criteria the user selected. I'd really rather not have to store all the field names and values and add them as parameters every time the app calls the report because they won't change. The problem is that user input fields may attempt sql injection and I need to prevent that. If I write a CREATE STORED PROCEDURE (programmatically) that includes: " WHERE projects.ProjectTypeID = :ProjectTypeID" and then add a parameter to the PDO (not the sproc): $ppd->bindValue(":ProjectTypeID", "17", PDO::PARAM_INT); I just want PDO to plunk the value 17 into the PDO label :ProjectTypeID If it would do that I'd have my stored procedure. From there all I'd have to do would be set the Next ID and the Limit in parameters ie: "CALL sprcReport37(0, 20)" This seems very simple to me. Again, the procedure would include multiple sql statements. When I try to do this using PDO the execute returns true and there are no errors but PDO will not create the stored procedure. requinix: "do anything special" Is writing a stored proc that special? It's not hard to do if I could just get past the sql injection. If I did I could recall it over and over and over with the sql: "CALL sprcReport37(0, 20)". That's a lot easier for me. Why is writing a stored procedure programmatically such a big deal? Can I programmatically create a stored procedure having multiple sql statements in PHP using PDO for MySql? OR - Can I programmatically create a stored procedure using anything that will protect against user input sql injection without making all the input values stored procedure parameters?

Uncle. I'll figure it out. Thanks

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.

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?

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(); }

Again, thanks. I realize it's difficult to answer me. The problem is that about 1,000 lines of PHP code are used to generate the query. The user creates the join tables and fields and well, it's complicated. Most queries won't really be that big, just diverse. It depends on all the choices the user makes within the app. As you suggest, I'll try different cases and test speeds. Thanks again for your help.

Thank you Barand: I'm surprised that the double JOIN on the oc_order_product table is faster than what I had in mind (not that I don't believe you 🙂 ). The filter may include both tables and joins on both tables and even joins on the joins. So, as you see, the need for speed is pretty critical. To clarify, you're saying put the entire filter into the subquery? I don't see why that would be a problem, just a bit more work for me lol.