Unable to use backoffice SQL Manager, getting error "Db::getArrray method can be used for SELECT queries only."

[papagino]

Hello people, today, I tried to use SQL Manager in the Backoffice to do an SQL query for an email campaign, but as soon as I click to view the results,  I get this error : "Db::getArrray method can be used for SELECT queries only."

If I try to export the query instead,  I get a "This site can’t be reached" page with this error:

The webpage at https://www.xxxxxxxxxxxxxxx/index.php?controller=AdminRequestSql&token=xxxxxxxxxxxxxxxxxxxxxxxxxx&id_request_sql=2&exportrequest_sql=1 might be temporarily down or it may have moved permanently to a new web address.
ERR_INVALID_RESPONSE

Errors logs shows this:

Stacktrace

#0 controllers/admin/AdminRequestSqlController.php(511)
#1 classes/db/Db.php(1100): ()
#2 controllers/admin/AdminRequestSqlController.php(511): DbCore->getArray()
#3 controllers/admin/AdminRequestSqlController.php(343): AdminRequestSqlControllerCore->generateExport()
#4 classes/controller/Controller.php(209): AdminRequestSqlControllerCore->initContent()
#5 classes/Dispatcher.php(858): ControllerCore->run()
#6 admin123/index.php(58): DispatcherCore->dispatch()

I'm on Thirtybees 1.6.0

Any idea why I get this error?

Any help would be appreciated

Cheers,

Edited 18 hours ago by papagino

[the.rampage.rado]

The SQL manager is heavily restricted in the operations it can do to limit exposure of the DB to bad actors.

Give us the query you want to run so we can check for a better version.

[papagino]

14 minutes ago, the.rampage.rado said:

The SQL manager is heavily restricted in the operations it can do to limit exposure of the DB to bad actors.

Give us the query you want to run so we can check for a better version.

... not sure what you mean with " for a better version." but I basically need these queries:  

id_customer, id_shop_group, id_lang, firstname, lastname, email, newsletter, date_add

[DRMasterChief]

I highly recommend using hosting-based SQL management for this kind of thing. You probably have access via cPanel or something similar. It offers extensive SQL functions and full access to everything. Don't forget to back up your data beforehand 😉

[papagino]

If I go in "Customers" in Backoffice and use "EXPORT TO SQL MANAGER" button, then it is working fine, I am able to see or export in CSV. 

So the problem seems to be only when you use the "Add new SQL query" inside the SQL Manager in Backoffice to create a new query from the "List of MySQL Tables" that I get the errors.

Not sure if it is a bug...

 

[the.rampage.rado]

As I said in the SQL manager, you are using forbidden statements or nested queries, etc.

The export functionality in Customers is prepared query that does not run through thirty bees' SQL manager.

As DRMasterChief said - if you have tool at hosting level similar to phpMyAdmin and you're confortable using it - do so but always keep a fresh copy of your db so not to end in a bad situation (only one deleted column in this table can brick your shop).

You want a list containing those columns?
 

SELECT 
  c.id_customer,
  c.id_shop_group,
  c.id_lang,
  c.firstname,
  c.lastname,
  c.email,
  c.newsletter,
  c.date_add
FROM ps_customer c;

Of course edit the table prefix to match yours.