SteemSQL Tutorial: How to Avoid SQL Injection?

injection

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.

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:

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 注入？