PHP Warning: mysqli::query(): (42000/1064): You have an error in your SQL syntax; check the manual...

jrr wrote: ↑

Tue Jan 14, 2025 2:50 pm

OC 3.0.4.0, PHP 8.x.
Got about 150 of these tonight (7:53-7:54PM) from a number of multiple IP addresses. Was this an attack or possibly an extension error. Have not added any new extensions in the past couple of days, nor reinstalling any though - and I've not seen this error message before.

Code: Select all

Error Message 	PHP Warning: mysqli::query(): (42000/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 '?', search_term = '' where tracking_id='258#'' at line 1 in /.../catalog_oc/system/library/db/mysqli.php on line 25 (tracking_id='258#' where # = 0 - 9)
URL 	https://flippers.com/catalog_oc/index.php?route='
Customer 	Guest Customer
IP 	161.77.235.150 (161.77.0.27)(161.77.143.64)(45.85.204.9)(45.85.205.10)(and more...)
User Agent 	Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/129.0.0.0 Safari/537.36  (<--this line never changed)
Referring URL 	https://flippers.com/catalog_oc/index.php?route='

Thanks!
John :-#(#

jrr wrote: ↑

Wed Jan 15, 2025 3:04 am

Right...

How do I find which extension might be creating this problem - if it is actually a problem and not the system trying to protect my site from an attach of some sort?

It was a one-off situation, hasn't happened before or since.

Thanks!

John :-#)#

jrr wrote: ↑

Tue Jan 14, 2025 2:50 pm

Code: Select all

Error Message 	PHP Warning: mysqli::query(): (42000/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 

paulfeakins wrote: ↑

Wed Jan 15, 2025 8:58 pm

jrr wrote: ↑

Tue Jan 14, 2025 2:50 pm

Code: Select all

Error Message 	PHP Warning: mysqli::query(): (42000/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 

My guess would be an extension you have installed is vulnerable to SQL injection and someone is trying to run SQL on your server through it. You need to get that fixed ASAP.

jrr wrote: ↑

Thu Jan 16, 2025 3:38 pm

paulfeakins wrote: ↑

Wed Jan 15, 2025 8:58 pm

jrr wrote: ↑

Tue Jan 14, 2025 2:50 pm

Code: Select all

Error Message 	PHP Warning: mysqli::query(): (42000/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 

My guess would be an extension you have installed is vulnerable to SQL injection and someone is trying to run SQL on your server through it. You need to get that fixed ASAP.

The only extensions I have that use the expression tracking_id are in admin - one is for checking who is online (I will send a note to the developer), the other is pp_express which is not enabled on my site.

I'm inclined to think the PayPal Express would be the target as it is more likely to be used by OpenCart sites...

Thanks!

John :-#)#

nonnedelectari wrote: ↑

Thu Jan 16, 2025 6:39 pm

Well, inclinations are fine but you have an sql construct which contains "where tracking_id = ...." somewhere and I don't see that in pp_express.

paulfeakins wrote: ↑

Thu Jan 16, 2025 6:51 pm

nonnedelectari wrote: ↑

Thu Jan 16, 2025 6:39 pm

Well, inclinations are fine but you have an sql construct which contains "where tracking_id = ...." somewhere and I don't see that in pp_express.

Indeed, and it's disabled.

ADD Creative wrote: ↑

Fri Jan 17, 2025 12:39 am

The tracking_id in admin/model/extension/payment/pp_express.php is not used in an SQL query, so it is not that. Also disabling a payment extension won't necessarily stop it being used in an attack.

Remember to also search the modifications directory and and vQmod cache.

As suggested by nonnedelectari, did you review your access logs to see what the original requests look like?

jrr wrote: ↑

Fri Jan 17, 2025 1:20 am

ADD Creative wrote: ↑

Fri Jan 17, 2025 12:39 am

The tracking_id in admin/model/extension/payment/pp_express.php is not used in an SQL query, so it is not that. Also disabling a payment extension won't necessarily stop it being used in an attack.

Remember to also search the modifications directory and and vQmod cache.

As suggested by nonnedelectari, did you review your access logs to see what the original requests look like?

What I did for my search was check my desktop mirror of my site for tracking_id and tracking_id= that search turned up only the two extensions. I then searched my DB backup for the same terms and that turned up nothing.

Not sure how to search the modifications directory and vQmod cache...

Store_Error.Log shows all the occurrences on Jan 13 for that message - no extra info that I can see.

So, not [solved] then? I'll delete that...

John :-#(#

nonnedelectari wrote: ↑

Fri Jan 17, 2025 9:05 am

because this part "'?', search_term = '' where tracking_id='258#'" on the error message might have been provided as a parameter in a request as opposed to being part of your source code.

ADD Creative wrote: ↑

Fri Jan 17, 2025 12:39 am

Also disabling a payment extension won't necessarily stop it being used in an attack.

khnaz35 wrote: ↑

Fri Jan 17, 2025 9:14 am

nonnedelectari wrote: ↑

Fri Jan 17, 2025 9:05 am

because this part "'?', search_term = '' where tracking_id='258#'" on the error message might have been provided as a parameter in a request as opposed to being part of your source code.

This could also mean a possible SQL injection attack or test. Can you confirm if the tracking_id='258#' was the same across all 100 request logs, or was it changing dynamically?

jrr wrote: ↑

Wed Jan 22, 2025 2:13 am

Dang - just got hit with around 240 attempts:

Code: Select all

PHP Warning: mysqli::query(): (42000/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 '?', search_term = '' where tracking_id='1018'' at line 1 in /.../catalog_oc/system/library/db/mysqli.php on line 25

This time they only tried tracking_id='1018'.

Sigh.

Don't people have better things to do than bugging small businesses?

throw new \Exception('Error: ' . $this->connection->error  . '<br />Error No: ' . $this->connection->errno . '<br />' . $sql);

nonnedelectari wrote: ↑

Wed Jan 22, 2025 9:18 am

On exception catching:

We use this in system/framework.php

Code: Select all

function exit_nicely () {
	$headers = array_change_key_case(apache_request_headers(),CASE_LOWER);
	$json_req = ((array_key_exists("accept",$headers) && stristr($headers['accept'],'application/json')) ? true : false);
	$ajax_req = ((array_key_exists("x-requested-with",$headers) && $headers['x-requested-with'] == 'XMLHttpRequest') ? true : false);
	error_log('Framework: exiting nicely');
	if ($json_req || $ajax_req) {
		error_log('Exception handler: Returning json error record to '.$_SERVER['REMOTE_ADDR'].' for '.$_SERVER['REQUEST_URI']);
		$json = array();
		$json['error']['warning'] = 'Sorry, we seem to be experiencing some difficulties, please try again or contact us directly.';
		$json['success'] = 'Sorry, we seem to be experiencing some difficulties, please try again or contact us directly.';
		$json_serial = json_encode($json);
		error_log($json_serial);
		header('Content-Type: application/json');
		echo $json_serial;
	} else {
		error_log('Exception Handler: Showing static maintenance page to '.$_SERVER['REMOTE_ADDR'].' for '.$_SERVER['REQUEST_URI']);
		ob_start();
		include('maintenance.html');
		$problem_page = ob_get_clean();
		http_response_code(503);
		echo $problem_page;
	}	
	die();	
}

set_exception_handler("handle_exception");
function handle_exception ($exception) {
	error_log($exception->getFile().' - line: '.$exception->getLine().' - Message: '.$exception->getMessage().' - '.$exception->getTraceAsString());
	exit_nicely();
}

catches the exceptions and returns a static maintenance page (maintenance.html) or a json encoded record with a non technical message in case an ajax request was made (takes care of those awkward json syntax error popups with html error messages).
In the meantime it logs the necessary info in your php error log.