Reliance on mysql shortcut evaluation can lead to an SQL error message

[pintobuck]

Can you help me understand this? Thank you.
This is the BFStop message on the website's home page:

1690 BIGINT UNSIGNED value is out of range in '((1 << (32 - substr('176.31.149.121',(locate('/','176.31.149.121') + 1),(length('176.31.149.121') - locate('/','176.31.149.121'))))) - 1)' SQL=SELECT id, ipaddress, crdate, duration FROM #__bfstop_bannedip b WHERE (ipaddress='109.163.234.8' OR (LENGTH(ipaddress) <= 18 AND LOCATE('/', ipaddress) != 0 AND (INET_ATON('109.163.234.8') & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)) = (INET_ATON(SUBSTR(ipaddress, 1, LOCATE("/", ipaddress)-1)) & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)))) AND (b.duration=0 OR DATE_ADD(b.crdate, INTERVAL b.duration MINUTE) >= '2016-10-08 20:45:23') AND NOT EXISTS (SELECT 1 FROM #__bfstop_unblock u WHERE b.id = u.block_id)

[pintobuck]

When I try to login via back end administrator, the screen is frozen on the same message, but also when I click on the settings cog wheel, I see: TPL_ISIS_EDIT_ACCOUNT, and TPL_ISI_LOGOUT. So maybe TPL_ISIS has been in the site.

[pintobuck]

Ok, I went to the BFStop wiki, did as it recommended. I deleted the banned IP number from mysql tbl_bfstop_bannedip. That opened up the website again. The banned IP came out of Spain, and I'm in the USA. So, I wonder why my USA IP was blocked so I could not get into the site

Do you think it was a hacking attempt, or just something I triggered because I was working on the site a lot?

[codeling]

So, I wonder why my USA IP was blocked so I could not get into the site

This is not an actual block, but rather looks like a bug in bfstop.

or just something I triggered because I was working on the site a lot?

By working, are you referring to working in the bfstop backend? If you possibly added an invalid subnet address (e.g. a currently unsupported IPv6 CIDR address, as reported here: #117) manually, then this could be a probable cause.

There is also a chance that there is some flaw in bfstop which created an invalid entry in the bannedip table which made that error appear. To debug that, it would however be necessary to get a log of the incident. When you say you deleted the banned ip number did you completely empty the table or just delete a single entry? Do you happen to have a backup of the content of the table when it wasn't working?

[pintobuck]

Thank you for the reply.
I was not working in bfstop. I not very technical and I don't have a good understanding of bfstop. It seemed like a good idea to install bfstop because the website is a social activist site that might be subject to attack. I was working in the Joomla back end to create the site, and in the front end to add content.

In the bannedip table, I deleted the single entry. It was the only entry.

I don't think I have a backup of anything when bfstop blocked the site because your wiki helped me clear the problem pretty quickly. If visiting the site will help, I can give you access.

Info: I used tor to access the site from the front end in my preceding work session. (Maybe that explains the IP from Spain.) Then I took a break. When I came back, without using tor, I noticed the site was blocked by bfstop.

[pintobuck]

Today, I manually entered an IP address into bfstop from the back end. Then this error message appeared in the back end:
An error has occurred.
1690 BIGINT UNSIGNED value is out of range in '((1 << (32 - substr('77.232.66.255',(locate('/','77.232.66.255') + 1),(length('77.232.66.255') - locate('/','77.232.66.255'))))) - 1)' SQL=SELECT id, ipaddress, crdate, duration FROM #__bfstop_bannedip b WHERE (ipaddress='77.232.66.255' OR (LENGTH(ipaddress) <= 18 AND LOCATE('/', ipaddress) != 0 AND (INET_ATON('77.232.66.255') & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)) = (INET_ATON(SUBSTR(ipaddress, 1, LOCATE("/", ipaddress)-1)) & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)))) AND (b.duration=0 OR DATE_ADD(b.crdate, INTERVAL b.duration MINUTE) >= '2016-10-11 23:31:39') AND NOT EXISTS (SELECT 1 FROM #__bfstop_unblock u WHERE b.id = u.block_id)

Return to Control Panel

Return to control panel did not work. I also used the admin menu Components/Brute Force Stop Administration, but that left the same error message on the page.

This error message appeared on the front end:

1690 BIGINT UNSIGNED value is out of range in '((1 << (32 - substr('77.232.66.255',(locate('/','77.232.66.255') + 1),(length('77.232.66.255') - locate('/','77.232.66.255'))))) - 1)' SQL=SELECT id, ipaddress, crdate, duration FROM #__bfstop_bannedip b WHERE (ipaddress='77.232.66.255' OR (LENGTH(ipaddress) <= 18 AND LOCATE('/', ipaddress) != 0 AND (INET_ATON('77.232.66.255') & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)) = (INET_ATON(SUBSTR(ipaddress, 1, LOCATE("/", ipaddress)-1)) & ~((1 << (32 - SUBSTR(ipaddress, LOCATE("/", ipaddress)+1, LENGTH(ipaddress)-LOCATE("/", ipaddress))))-1)))) AND (b.duration=0 OR DATE_ADD(b.crdate, INTERVAL b.duration MINUTE) >= '2016-10-11 23:29:45') AND NOT EXISTS (SELECT 1 FROM #__bfstop_unblock u WHERE b.id = u.block_id)

---

MySql tbl_bfstop_bannedip had a single entry:
id ipaddress crdate duration
1 77.232.66.255 2016-10-11 00:00:00 0

I deleted that row entry and everything returned to normal on my website.

[pintobuck]

When I re-entered 77.232.66.255 into bfstop administration blocked IPs, the same error messages reappeared.

[pintobuck]

Additional info:
The second event (IP 77.232.66.255) was on a different website than the first even (IP 176.31.149.121).

[n-oir]

I'm very interested in this issue, as I'm developer and I installed BFStop about 20 times in different Joomla projects. So I decided to check this bug. I manually entered this evil IP 77.232.66.255 from the back end... and nothing happend! All work perfectly - frontend, backend, plugin. The same was with 176.31.149.121. I used my last working Joomla project for this test (finished 3 months ago, Joomla 3.6.2 and BFStop 1.3 stable) . MySQL 5.6 utf8mb4.

[codeling]

@pintobuck what database software / version are you using? postgres by any chance? Or if mysql/mariadb, which version?

[pintobuck]

My websites are on the hosting service at servage.net, which uses MySQL v.5

[pintobuck]

I used their auto install tool for Joomla 3.5 and then used Joomla's auto update.

[codeling]

MySQL v.5 is quite unspecific, what "subversion" is it? (e.g. 5.0? 5.5?). servage.net homepage isn't very helpful in that regard - it says 5.0 and higher, so you'd have to check which version you have (see e.g https://geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks/how_to_check_mysql_version_number.php)

[pintobuck]

Thank you for reference to the tech info page. This is what I found at Servage.net:

MySQL
Server version: 5.5.35-MariaDB-1~squeeze
Protocol version: 10
MySQL charset: UTF-8 Unicode (utf8)

Web server
Apache
MySQL client version: 5.5.35-MariaDB
PHP extension: mysqli

[codeling]

I suspect this to be an expression evaluation issue - as the expression causing the problem ('((1 << (32 - substr('176.31.149.121',(locate('/','176.31.149.121') + 1),(length('176.31.149.121') - locate('/','176.31.149.121'))))) - 1)'
should only be evaluated if there is a slash in the IP address (signifying a subnet as opposed to a single IP address) as checked by LOCATE('/', ipaddress) != 0 before. But as I found out there is no guarantee for short-circuit evaluation in mysql/mariadb, and under some circumstances apparently the current where clause isn't short-circuit evaluated here. So far with the technical side of my current understanding of the problem.

Unfortunately I cannot reproduce this issue on my systems at the moment; I will nevertheless try to implement a fix for this in the next version. At the moment, to me the best fix seems to be to separate IP addresses and subnet blocks entirely into different tables, so that the checks can be separated and don't need to rely on short-circuit evaluation.

I will let you know once the fix is available so that you can test it on your system; as I said above, I cannot reproduce it, so I would need your help in making sure that I have fixed it. Please note that it might take some time, as I am extremely busy at the moment with other things. I do expect to have something until the end of the year - please be patient!

[pintobuck]

I would be happy to test the update whenever you're ready.

[codeling]

This might be fixed now. Could you test by applying the changes from here:
2b01a44
?

[codeling]

@pintobuck The new release is available, it should address this issue: https://github.com/codeling/bfstop/releases/tag/1.4.1

Please comment here or open a new issue if you should still encounter any problems with that new version!

[pintobuck]

I installed bfstop release tag 1.4.1 on my website on the shared hosting service at Servage.net, and everything is working fine. Thank you for providing the fix.

[codeling]

Glad to hear that it works for you now!

[pintobuck]

Hello, this error message returned again:
1690 BIGINT UNSIGNED value is out of range in '((1 << (32 - substr .... I did not copy the rest of it.
I was locked out of Admin until I disabled Brute Force Stop via msql, tbl_extensions.

Info: I previously upgraded to PHP 7.0.

bfstop info:
Brute Force Stop Administration
Administrator Component 1.4.0 May 2013
Brute Force Stop Package
Site Package 1.4.1 May 2013
System - Brute Force Stop
Site Plugin 1.4.1 November 2012

Server & Joomla Info:
PHP Built On Linux node2 2.100.4-1-amd64-grsec #1 SMP Tue Dec 28 21:57:50 CET 2010 x86_64
Database Version 5.5.35-MariaDB-1~squeeze
Database Collation latin1_swedish_ci
Database Connection Collation utf8mb4_general_ci
PHP Version 7.0.98-servage1
Web Server Apache
WebServer to PHP Interface apache2handler
Joomla! Version Joomla! 3.7.0 Stable [ Amani ] 25-April-2017 15:36 GMT
Joomla! Platform Version Joomla Platform 13.1.0 Stable [ Curiosity ] 24-Apr-2013 00:00 GMT
User Agent Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; LCJB; rv:11.0) like Gecko

On another website with identical setup, I could not login as admin due to invalid security token. I don't know if that was related to bfstop, but the problem went away after I disabled bfstop via tbl_extensions.

[codeling]

Hello, this error message returned again:
1690 BIGINT UNSIGNED value is out of range in '((1 << (32 - substr .... I did not copy the rest of it.
I was locked out of Admin until I disabled Brute Force Stop via msql, tbl_extensions.

So it was working for a while with 1.4.1 and now has stopped working again? Anything in the bfstop logs ?

Have you tried entering any IP address in the backend before with version 1.4.1?

I could not login as admin due to invalid security token. I don't know if that was related to bfstop, but the problem went away after I disabled bfstop via tbl_extensions.

That is very strange. I have not seen any other reports of this yet, and bfstop doesn't read, set or modify any security tokens...