Raw Sql Query Module

[math_php]

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)

[Pablus]

excellent module, only one detail, once you have a syntax error, you can not go back and fix it!

[math_php]

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

[Rhapsody]

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.

[math_php]

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....

[Ryowbw]

great .. finally find it here ! thanks for your great sharing!

[math_php]

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

[Rhapsody]

@ 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"!

[math_php]

 

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

[Rhapsody]

@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)

[math_php]

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

[sadlyblue]

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.

[Rhapsody]

@ 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

[Rhapsody]

@ 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!

[math_php]

@ 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

[Rhapsody]

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.

[math_php]

@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

[math_php]

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

[Rhapsody]

@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.

[math_php]

Thanks to Rhapsody for providing access to his server.

 

We found out that 'readfile()' is disabled on hosting for security reason.

 

Best regards

[Pablus]

I have added a small contribution: possibility of back office translation and spanish translation

rawsqltool.php

es.php

[math_php]

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)

[math_php]

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

[betisimo76]

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.

[Leo GB]

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

[math_php]

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

[Leo GB]

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

[philhusky]

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

[math_php]

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

[philhusky]

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

[morten.c]

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

[math_php]

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

[Leo GB]

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

[pvip]

Hi! sorry for my english!

in prestashop 1.6 modul is not instaling. you can make this module for prestashop 1/6?

[math_php]

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

[calindoudou]

Good evening,

I have little question in June , I Am rawsql you under 1.23 and thank you for the great non Module
My question is I have SEVERAL Queries and I Can delete ( car SOME Were the test) can not mays
I uninstalled and reinstalled But that has nothing changer , How ?

Thank you for your answers .

( Translated with Google)

[math_php]

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

[calindoudou]

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 besoin
et 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

[math_php]

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)

[calindoudou]

Super, j'ai réussi.

Bon pas avec ta commande en allant directement dans le phpmyadmin
mais c'est le resultat qui compte

 

Merci beaucoup