My SteemSQL scripts suddenly stopped working a few hours ago and I thought at first it was the connectivity issues until I found out the same queries run just fine on my other VPS machines.
The particular error is:
conn = pymssql.connect(host, username, password, database) File "pymssql.pyx", line 641, in pymssql.connect (pymssql.c:10824) pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (sql.steemsql.com:1433)\nNet-Lib error during Connection timed out (110)\n')
I contacted the author of STEEMSQL @arcange and he confirmed that:
Several IP addresses have been blocked because queries issued are considered as SQL Injection attempts by the IPS. You IP address is being blocked by my IPS (Intrusion Prevention System) because the SQL queries you issue are considered as SQL Injection exploit: `EXPLOIT MS-SQL SQL Injection closing string plus line comment, proto:TCP, ip/port:XXX.XXX.XXX.XXX
I have a few handy tools that invoke the STEEMSQL queries and it looks like some hackers were trying to inject the query, for example, he/she may put
1 | '; DROP TABLE Comments; GO;-- |
'; DROP TABLE Comments; GO;--
in the field of steem ID. So the query looks malicious to the firewall, which will, therefore, block the IP permanently unless @arcange unblocks it.
There are two fixes. The recommended on is to use the SQL parameter via something like
1 | SELECT field FROM table WHERE author = @account |
SELECT field FROM table WHERE author = @account
This will avoid the SQL injection from
1 | SELECT author FROM Comments WHERE author ='{0}' |
SELECT author FROM Comments WHERE author ='{0}'
to:
1 | SELECT author FROM Comments WHERE author =''; DROP TABLE Comments; GO;--' |
SELECT author FROM Comments WHERE author =''; DROP TABLE Comments; GO;--'
if the user maliciously enters:
1 | '; DROP TABLE Comments; GO;-- |
'; DROP TABLE Comments; GO;--
And, there is a second method which will discard the query if the field is invalid, for example, you can use Python function to check a valid steem ID that contains only lowercase, digits, dot and dash.
1 2 3 4 5 6 | def valid_id(s): if len(s) == 0: return False for i in s: if not (i.islower() or i.isdigit() or i == '-' or i == '.'): return False return True |
def valid_id(s): if len(s) == 0: return False for i in s: if not (i.islower() or i.isdigit() or i == '-' or i == '.'): return False return True
Similarly, in PHP, this can be checked as well (the Python version can also use a regular expression but it needs import re:
1 2 3 4 5 6 7 | function isValidId($str) { $str = trim($str); if (preg_match('/^[a-z0-9\-\.]+$/', $str)) { return 1; } return 0; } |
function isValidId($str) { $str = trim($str); if (preg_match('/^[a-z0-9\-\.]+$/', $str)) { return 1; } return 0; }
So, next time when somebody tries to inject SQL, the API servers will throw out 503 Service Temporarily Unavailable instead of trying to contact the SteemSQL server.
You may also like: STEEMSQL 教程 – 如何避免 SQL 注入?
STEEM SQL Tutorial
- SteemSQL Tutorial: How to Get Random Posts on SteemIt?
- SteemSQL Tutorial: How to Get Authors Order By Potential Payout in Last 7 days?
- SteemSQL Tutorial: How to Get Historic Posts of Today on SteemIt?
- SteemSQL Tutorial: How to Calculate Monthly Income on STEEMIT?
- SteemSQL Tutorial: Can we Really Recover Deleted Comments/Posts on STEEMIT?
- SteemSQL Tutorial: What are the Outgoing Votes for Big Whales?
- SteemSQL Tutorial: Count Total Interests Sent
- SteemSQL Tutorial: Finding Inactive Steemians that You Follow
- SteemSQL Tutorial: How to Fix “JSON text is not properly formated. Unexpected character ‘.’ is found at position 0.”?
- SteemSQL Tutorial: How to Get the Most Payout Authors in History?
- SteemSQL Tutorial: Get Most Single Payout Authors
- SteemSQL Tutorial: How to Avoid SQL Injection?
- SteemSQL Tutorial: I have spent 800 SBD (7000+ USD) buying votes!
- SteemSQL Tutorial: How to Check If SteemSQL is Synchronized with Steem Blockchain?
–EOF (The Ultimate Computing & Technology Blog) —
loading...
Last Post: Should you use a magic number in unit tests?
Next Post: PHP Function to Get Exchange Rate Between Cryptocurrency (BTC, LTC, ETH) to Fiat Currency?