Jump to content

SLSCoder

Members
  • Posts

    30
  • Joined

  • Last visited

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

SLSCoder's Achievements

Member

Member (2/5)

0

Reputation

1

Community Answers

  1. 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.
  2. 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.
  3. 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.
  4. 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?
  5. 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
  6. 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.
  7. 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?
  8. 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.
  9. 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?
  10. 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(); }
  11. 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.
  12. 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.
  13. I've got a problem with this. The oc_order will have a filter on it and the fields don't match the fields being requested. I realize I could make sure all the filter fields are included in the derived table but that gets involved and I'd rather not. My intention is to reverse the JOIN and then add the whole table in another JOIN: . . . FROM oc_order_product INNER JOIN (SELECT order_id FROM oc_order ORDER BY order_id LIMIT 0, 2) AS tblLimit ON oc_order_product.order_id = tblLimit.order_id INNER JOIN oc_order ON oc_order_product.order_id = oc_order.order_id WHERE oc_order.lastname = 'Norder'; I could alternatively add the requested fields to the derived table, omit the second JOIN and write the filter like: WHERE oc_order_product.order_id IN(SELECT order_id FROM oc_order WHERE oc_order.lastname = 'Norder') I'm thinking that using the second JOIN will be at least as fast and the second JOIN would be easier for me. Can I do it this way? Is the second JOIN at least as fast as the alternative using IN?
  14. Thanks for responding - again. The code is pretty complex due to the options given to the user but I think I can pull this off. Once we simplified it to a basic orders -> items query it was much easier to see. I'm kind of kicking myself for not seeing it. The solution you gave me is easy to implement. Thanks again for your help.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.