math_php Posted October 24, 2012 Posted October 24, 2012 (edited) Hi, This module is aimed to type direct sql database request . Result is displayed if it is a 'Select' or a 'show' request, else the request will be executed /!\ (Truncate, delete, update ...) Be carefull and make back up of your database, before lauching in 'not so sure sql request' or 'there is no Where in my delete request' Tested with 1.5, 1.49, 1.48. 'More secure version' 1.1 'Save my query' version 1.2 'beta csv export' version 1.2.1 'Zip and secured export directory' version 1.22 'Spanish translation, checkbox to enable/disable file zipping' version 1.23 Hoping it will be usefull rawsqltool-1.22.zip rawsqltool-1_23.zip Edited April 5, 2013 by math_php (see edit history) Share this post Link to post Share on other sites More sharing options...
math_php Posted November 8, 2012 Posted November 8, 2012 excellent module, only one detail, once you have a syntax error, you can not go back and fix it! Thanks Pablus, I have no solution for the moment to catch sql errors, especially when sql display error is turned on, I will try to find one. I made a new version with a selector that list all tables ( for mysql only). And remember it is a raw and wild module, aimed to give the possibility to rampage database or to fix it Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted November 8, 2012 Posted November 8, 2012 This is a very interesting module. May I propose a suggested enhancement? Allow the user to "be protected" from stupid sql errors that modify the database. This could be done by forcing the user to check a box only if it is really desired to modify the database, otherwise only allow SQL commands to query and display output. It may require parsing the input and searching for certain strings that would alter the database. If any of the strings were found, it would return a "modifying database not permitted" message. Share this post Link to post Share on other sites More sharing options...
math_php Posted November 8, 2012 Posted November 8, 2012 This is a very interesting module. May I propose a suggested enhancement? Allow the user to "be protected" from stupid sql errors that modify the database. This could be done by forcing the user to check a box only if it is really desired to modify the database, otherwise only allow SQL commands to query and display output. It may require parsing the input and searching for certain strings that would alter the database. If any of the strings were found, it would return a "modifying database not permitted" message. Hi Rhapsody, Yes I thought it would be a good thing to add security, this module is a savage beast we must protect the inocent Last time when I was testing 1.5.2 I saw that you can make 'Select request' only in Prestashop standard 'Sql Manager' in a safer way than mine. Allowing Delete, Update, Truncate to admin only and letting 'Show' and 'Select' for other employee could be a good solution. Also a javascript warning before launching modification request would also be fine. I made this module, because I wanted a shortcut to phpMyAdmin wich bored me with user/password/url/latency.... Share this post Link to post Share on other sites More sharing options...
Ryowbw Posted November 9, 2012 Posted November 9, 2012 great .. finally find it here ! thanks for your great sharing! Share this post Link to post Share on other sites More sharing options...
math_php Posted November 9, 2012 Posted November 9, 2012 Hi guys, New 1.1 version : - only employee with 'admin' profile are now allowed to do 'modifications' queries, other profiles can only make select requests - admin are warned before launching 'modifications' queries - choose a table name in the selector, click on 'add' button to add `table_name` in your request edit. Best regards Ps : if you like this, like this 1 Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 10, 2013 Posted February 10, 2013 @ math_php - since I've played with the SQL Manager in 1.5.3 I have grown to like the ability to save the queries, and the ability to export to Excel. I have 3 shops that run on PS 1.4.9.0 that I don't plan on upgrading because of investments made that are not compatible with the upgrade to 1.5 Would it be possible for you to strip some of the code in the 1.5.3 RequestSql.php and AdminRequestSqlController.php files to add the save query and export to Excel (CSV) functions to your module? I am currently using phpmyadmin to do things that would be more convenient in the BO with your module. Thanks and I've "liked it"! Share this post Link to post Share on other sites More sharing options...
math_php Posted February 14, 2013 Posted February 14, 2013 Would it be possible for you to strip some of the code in the 1.5.3 RequestSql.php and AdminRequestSqlController.php files to add the save query and export to Excel (CSV) functions to your module? I am currently using phpmyadmin to do things that would be more convenient in the BO with your module. Hi Rhapsody, You can now save your queries in version 1.2. Make a reset of the newly downloaded module, it will then create the needed table to save your work. Testing feedback will be apreciated. Csv export will come soon Best regards 1 Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 14, 2013 Posted February 14, 2013 (edited) @math_php, Absolutely Awesome! Tested on 1.4.9.- successfully with queries saved. I look forward to the CSV export upgrade. This module makes it not worth the hassle for upgrading working 1.4 shops to 1.5! Suggested enhancements. 1. Ability to edit and delete existing saved query 2. Save by overwrite existing query, or a new name to create a new query based on what is already saved Edited February 14, 2013 by Rhapsody (see edit history) Share this post Link to post Share on other sites More sharing options...
math_php Posted February 15, 2013 Posted February 15, 2013 Hi, Csv export have been added for saved queries and buttons will only appear in this case. There are 2 csv exports type : - 'standard csv' : fields separated by semi colon and 'end of line' with \n - 'excel csv' : that will allow to get 'good' import in Excel A zipped xml export could be a last worthwile effort, if asked nicely or with lot of cash or if none is interrested. Lots of improvment could and should be made to this module. Interface is messy as csv format is. Use with care. Best regards Share this post Link to post Share on other sites More sharing options...
sadlyblue Posted February 15, 2013 Posted February 15, 2013 Thanks for the module. Looks great. Just an idea, You could add some ready made queries just as an example for those that don't know that much about sql queries. Just a few simple ones. The safe mode would be a good idea too. Again, thanks for sharing. Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 15, 2013 Posted February 15, 2013 @ math_php - I installed and tried version 1.21 but the csv output files are 0 length with no content. The output to the screen works fine. 1. You may want to update the revision inside your config.xml file (I did it manually) - because at first I thought the old version module was installed. The new module was verified as installed. 2. I did both an overwrite of the old module, and uninstall old, reinstall new - same result. 3. Here is the query I used to test - this outputs some customer info with orders in 2013: SELECT (c.id_customer) "Customer", (c.firstname) "First", (c.lastname) "Last", (c.email) "Email", (o.id_order) "Order", (n.product_name) "Product Name" , (n.product_reference) "Prod Ref." FROM `ps_orders` o LEFT JOIN `ps_customer` c ON c.`id_customer` = o.`id_customer` LEFT JOIN `ps_order_detail` n ON n.`id_order` = o.`id_order` WHERE o.`valid`=1 AND o.`invoice_date` BETWEEN "2013-01-01" AND "2013-12-31" ORDER BY n.product_reference @sadlyblue - see this post for samples: http://www.prestashop.com/forums/index.php?/topic/2231[spam-filter]helping-others-using-sql-manager-in-ps-15/page__view__findpost__p__1099471 Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 15, 2013 Posted February 15, 2013 @ math_php - I installed and tried version 1.21 on another shop and the csv outpout works fine. Both shops are 1.4.9.0. I will troubleshoot this weekend and let you know if the problem is unique to this one shop, or there is a fix that might need to be made. It works great - Thanks! Share this post Link to post Share on other sites More sharing options...
math_php Posted February 15, 2013 Posted February 15, 2013 @ math_php - I installed and tried version 1.21 on another shop and the csv outpout works fine. Both shops are 1.4.9.0. I will troubleshoot this weekend and let you know if the problem is unique to this one shop, or there is a fix that might need to be made. It works great - Thanks! Hi Rhapsody, Yes config.xml is a mistake, I should get rid of it in published version. For file output, I should test if folder is writable and check what download size is authorized. Ziping all files would also be a good thing. Best regards Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 15, 2013 Posted February 15, 2013 For file output, I should test if folder is writable and check what download size is authorized. Installed on a 3rd 1.4.9.0 store with no problem, so it appears to be unique to the one install. All 3 are hosted on Linux servers. Are you writing the file to temp directory on the server? If so, I can check the directory and associated rights and compare with the other installs. The module is installed in a directory that has 755 rights, and the files 644. The file size is very small (<5kb) so the size check probably isn't an issue. Share this post Link to post Share on other sites More sharing options...
math_php Posted February 15, 2013 Posted February 15, 2013 @Rhapsody. I write in module directory and searching for a better location. File is destroyed right after being transmitted (for security purpose), perhaps it creates timing problem like destroy before transmit. For example Prestashop write exports but does not destroy it. In 1.4 there is an admin___/import folder, in 1.5 there is a admin___/export I could use but this could create some 'noise' Perhaps a folder with random name generated like 'gdoaerkfdgvodsiufsqhjggsf' in module directory could be a good security solution. Log of failure and warning could help and prevent such failure. Meanwhile zipping has been added and will be published. Thanks for feedbacks Share this post Link to post Share on other sites More sharing options...
math_php Posted February 18, 2013 Posted February 18, 2013 Hi Version 1.22 now compress csv export in zip format. A folder has been added to secure your export. This directory is protected by .htaccess rule : 'Deny from all', then if you have not admin token, it is going to be hard to steal your datas. If you have better security idea let me know. @ math_php - I installed and tried version 1.21 on another shop and the csv outpout works fine. Both shops are 1.4.9.0. I will troubleshoot this weekend and let you know if the problem is unique to this one shop, or there is a fix that might need to be made. @Rhapsody, new export folder in module may solve the problem with one of your shop. Best regards Share this post Link to post Share on other sites More sharing options...
Rhapsody Posted February 18, 2013 Posted February 18, 2013 @Rhapsody, new export folder in module may solve the problem with one of your shop. Best regards @math_php - This one shop is still a problem. I am going to create a backup of it as a test shop off line and will play with it to make some changes and see if I can figure out what the issue is. If you want, once I have it setup I can give you access to allow you to troubleshoot and see what might be done. PM me and we can coordinate. Share this post Link to post Share on other sites More sharing options...
math_php Posted February 19, 2013 Posted February 19, 2013 Thanks to Rhapsody for providing access to his server. We found out that 'readfile()' is disabled on hosting for security reason. Best regards Share this post Link to post Share on other sites More sharing options...
math_php Posted March 5, 2013 Posted March 5, 2013 (edited) Thanks Pablus, I should use Prestashop translation instruction but I always end up without. Peligroso módulo Viva Argentina Edited March 5, 2013 by math_php (see edit history) Share this post Link to post Share on other sites More sharing options...
math_php Posted April 5, 2013 Posted April 5, 2013 Hi, Version 1.23 is available with : - A 'Configuration section' has been added to clearify somewhat the interface - Spanish translation from Pablus - Checkbox to enable/disable export file to be zipped Xml output is still a problem because columns name of your requests may not be fully compatible with xml naming rules. Regards 1 Share this post Link to post Share on other sites More sharing options...
betisimo76 Posted November 24, 2013 Posted November 24, 2013 Hello math_php I've installed your version 1._23 and, when I try to show any SQL query, I always have same answer: Disabled funcion on your hosting: And no more. Do you know what functions I have to Disabled function on your hosting : I've seen in my php.ini->disabled_functions, I haven't got any string there, so I don't know what is the issue? Please, can you help me? Thanks a lot. Share this post Link to post Share on other sites More sharing options...
Leo GB Posted December 19, 2013 Posted December 19, 2013 Hello Math_php, As we have been talking, I include the following changes to the module: - Editing of selected query (case sensitive). - Delete of selected query. - Use of bind variables by the following syntax: :myvar: To use bind variables, first edit the query "where" clause (ex: where id_order > :orderId:). Then save the query. When reloading, select the modified query. On selection, below the sql textarea, a variable section will appear. Just fill in the values the same way they would be typed in SQL textarea. So, the following files have been changed: - rawsqltool.php - rawsqltool.js - export.php Tested under 1.4.5.1, the changes are in development phase, so comments are wellcome. Have a Nice Christmas! Leo rawsqltool_changes.zip Share this post Link to post Share on other sites More sharing options...
math_php Posted December 20, 2013 Posted December 20, 2013 Hello Leo, Deleting saved queries seemed to work fine, nice feature that was often asked As computer said : Notice: Undefined offset: 1 in C:\wamp\www\prestashop15\modules\rawsqltool\rawsqltool.php on line 427 $arr_vars[] may sometimes be empty I went in an infinite loop when using: select * from `ps_currency` where name='name:' rather than this select * from `ps_currency` where name=':name:' my fault. Nice features Have a nice Christmas. Math Share this post Link to post Share on other sites More sharing options...
Leo GB Posted January 7, 2014 Posted January 7, 2014 Hello Math, Thanks for your feedback, Attached the last version of the module includes: - Fixed the warning about arr_vars. - Fixed infinite loop when variable token is missing. A "wrong variable configuration" message is displayed when saving the sql if the variable pattern does not match. - Changed name of exported file/zipped: The new filename pattern is YYYY-MM-DD_HHMMSS_queryName. - Translation to Catalan. With these changes Rawsqltool 1.24 (aka Rawsqltool Sobrassada) is on the road! :-) Hope you like the changes. Comments are welcome. Wish all of you a happy new year, and sorry for the delay of my post. Leo rawsqltool.zip Share this post Link to post Share on other sites More sharing options...
philhusky Posted March 4, 2014 Posted March 4, 2014 Hello Math, Thank you for this module, the query works and display in browser but export file is always empty. I have not Excel logo . I have set chmod at 755 What i do wrong ? Also how can i modify the module to have only "select" request avaible (for safety). Ps 1.4.11 Best regards Share this post Link to post Share on other sites More sharing options...
math_php Posted March 6, 2014 Posted March 6, 2014 Hi Philhusky, You must first save your request with 'save as' with name . Then you will see export button. I admit that the module should specify this kind of information. Do not expect safety here, it is not even sql injection, it is direct sql. The danger /!\ You could get rid off 'Request' text area and replace it by a select list (for example 1 for all client list with phone, 2 for product from...) and then hard code those requests in module. Best regards Share this post Link to post Share on other sites More sharing options...
philhusky Posted March 6, 2014 Posted March 6, 2014 Hello Math Thank you for your so quick answer. Now it's ok and i will hard code the requests in module, as you explain. Thank you again Best regards Phil Share this post Link to post Share on other sites More sharing options...
morten.c Posted March 10, 2014 Posted March 10, 2014 Hey math, first of all: thanks for your work, I use this module for complex cutom exports and it saved me a lot of time. But there seems to be a problem with the zip file of your latest version (couldn't install it in backend, if I extract it manually some files have a sizes of 0), could you please check the zip and reupload it? Thank you very much for your help! best regards, morten Share this post Link to post Share on other sites More sharing options...
math_php Posted March 11, 2014 Posted March 11, 2014 Hey math, first of all: thanks for your work, I use this module for complex cutom exports and it saved me a lot of time. But there seems to be a problem with the zip file of your latest version (couldn't install it in backend, if I extract it manually some files have a sizes of 0), could you please check the zip and reupload it? Thank you very much for your help! best regards, morten Hi Morten, Use my last version at the very beginnig of the post. The version of Leo GB is not finished, I am waiting for him to publish last version. Last officiel version is published by me and is 1.23 click here Regards Share this post Link to post Share on other sites More sharing options...
Leo GB Posted September 22, 2014 Posted September 22, 2014 Hello Math, Yes, I'm alive. I'm very sorry for this big delay in replying, but after finishing my project about Prestashop I had to look after some personal and professional stuff. That's why until now I could not get a minute for the Community. I've checked the changes of your last message. I would like to notice a change in update_query() and in insert_query() that I had to code in my project/version. In these functions, I've had to change the call to escape the sql query (addslashes() instead of pSQL()). It worked in my tests. Now I don't remember an example but I think I used this way to have custom column names in result grid. Please, feel free to contact me if the changes dont fit the standards or if I can help anywhere else. I would like to help 1.24 into the light! Kind regards, Leo rawsqltool.zip Share this post Link to post Share on other sites More sharing options...
pvip Posted April 6, 2015 Posted April 6, 2015 Hi! sorry for my english! in prestashop 1.6 modul is not instaling. you can make this module for prestashop 1/6? Share this post Link to post Share on other sites More sharing options...
math_php Posted April 9, 2015 Posted April 9, 2015 Hi! sorry for my english! in prestashop 1.6 modul is not instaling. you can make this module for prestashop 1/6? Hi pvip, Do not be sorry for your english, we are few to be english speaking people you know Anyway let's pretend we can write english, eyes will be bleeding Version 1.23 at the very beginning of this topic is working well on my 1.6.0.14. Prestashop I did not test Leo version of 22 september, I will. Perhaps the display in back office is different, but in my case it looks ok. Cheers Share this post Link to post Share on other sites More sharing options...
calindoudou Posted September 9, 2015 Posted September 9, 2015 Good evening,I have little question in June , I Am rawsql you under 1.23 and thank you for the great non ModuleMy question is I have SEVERAL Queries and I Can delete ( car SOME Were the test) can not maysI uninstalled and reinstalled But that has nothing changer , How ?Thank you for your answers .( Translated with Google) Share this post Link to post Share on other sites More sharing options...
math_php Posted September 10, 2015 Posted September 10, 2015 Hi Calindoudou, Si je retraduis de l'anglais, traduit automatiquement de ta question, cela commence comme ça : ' J'ai une petite question en juin, je suis rawsql...' N'hésite pas à reposer ta question directement en français. Sinon en parent prévoyant j'avais cherché avant le drame, qui ne s'est jamais produit, le doudou de ma fille. Je l'avais pas trouvé à l'époque mais là si. Sympa ton site. Math Share this post Link to post Share on other sites More sharing options...
calindoudou Posted September 10, 2015 Posted September 10, 2015 Bonjour Math, Effectivement ca le fait la traduction merci pour le site Ma question etait en fait que j'ai fait pas mal de test qui doivent être enregistrer a chaque fois , et je voulais les apurés pour ne garder que les requetes dont j'ai réellement besoinet je ne sais comment faire. Car même en désinstallant/supprimant et réinstallant , elles reviennent, du coup je me demandais si il n'y a avait pas un "truc" Merci Share this post Link to post Share on other sites More sharing options...
math_php Posted September 10, 2015 Posted September 10, 2015 (edited) Bonjour Calindoudou, Tu dois avoir une table mysql ps_rawsqltool ou quelque chose comme ça, suivant le préfix des tables de ton installation. Cette table contient les requêtes enregistrées pour le module et n'est destinée qu'a ça. Tu peux la vider en faisant une requête : TRUNCATE ps_rawsqltool Have a good day Edited September 10, 2015 by math_php (see edit history) Share this post Link to post Share on other sites More sharing options...
calindoudou Posted September 10, 2015 Posted September 10, 2015 Super, j'ai réussi. Bon pas avec ta commande en allant directement dans le phpmyadminmais c'est le resultat qui compte Merci beaucoup Share this post Link to post Share on other sites More sharing options...
Recommended Posts