0

Trying to run this Python file python pid_info.py 12345 which looks like

#!/usr/bin/env python
import subprocess
import sys, getopt

# add if -b or -e then look for username/email like etc... 
# figure out how to store the db creds in separate file 
class color:
   PURPLE = '\033[95m'
   CYAN = '\033[96m'
   DARKCYAN = '\033[36m'
   BLUE = '\033[94m'
   GREEN = '\033[92m'
   YELLOW = '\033[93m'
   RED = '\033[91m'
   BOLD = '\033[1m'
   UNDERLINE = '\033[4m'
   FLASH = '\033[0.5m'
   END = '\033[0m'

# DB info:
host = 
db=
user=
password=
# take the argument provided by user
UN=str(sys.argv[1])    
# SQL query to return user info + role
f_statement1 = """ set nocount on; set ansi_warnings off; 
SELECT 
pl.placement_id PID, pl.placement_name, p.partner_name Publisher, pc.description Platform_client, pit.description +'/'+ dt.description  Integration_Device
FROM placement pl 
JOIN partner p ON pl.partner_id = p.partner_id 
JOIN platform_client pc ON p.platform_client_id = pc.platform_client_id
JOIN placement_integration_type_assoc pita ON pl.placement_id = pita.placement_id 
JOIN placement_integration_type pit ON pita.placement_integration_type_id = pit.placement_integration_type_id
JOIN device_type dt ON pl.device_type_id = dt.device_type_id 
WHERE pit.active=1
AND pita.active=1 AND pl.placement_id = """ + str(UN)

f_statement2 = """ set nocount on; set ansi_warnings off; 
SELECT 
pl.max_ad_duration Seconds, c.abbreviation Country,
CASE WHEN passback_allowed=0 THEN 'GUARANTEED' ELSE 'PASSBACK' END AS Buy_Type, 
CASE WHEN pl.skippable=0 THEN 'Non-Skippable' ELSE 'Skippable' END AS Skippable,
CASE WHEN pl.active=1 THEN 'ACTIVE' ELSE 'NOT_ACTIVE' END AS Status
FROM placement pl 
JOIN country c ON pl.country_id = c.country_id
WHERE   pl.placement_id =""" + str(UN)

f_statement3 = """ set nocount on; set ansi_warnings off;
SELECT url_expression FROM AN_MAIN..placement_domain_whitelist
WHERE active=1 and placement_id =""" + str(UN)

# run the first query
print('\n')
print(color.UNDERLINE + color.BOLD + "Results for PID " + str(UN) + ":" + color.END)
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement1, "-Y","30", "-s", "|" ])
print('\n')
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement2, "-Y","30", "-s", "|" ])
print('\n')
print(color.UNDERLINE + color.BOLD + "Whitelist for PID " + str(UN) + ":" + color.END)
print('\n')
results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement3, "-Y","30", "-s", "|" ])
print('\n')

input ()

And when I do I get the error

Results for PID 12345:
Traceback (most recent call last):
  File "pid_info.py", line 57, in <module>
    results1=subprocess.call(["sqlcmd", "-S", host, "-U",user, "-P",password, "-d",db, "-Q", f_statement1, "-Y","30", "-s", "|" ])
  File "/usr/lib/python2.7/subprocess.py", line 523, in call
    return Popen(*popenargs, **kwargs).wait()
  File "/usr/lib/python2.7/subprocess.py", line 711, in __init__
    errread, errwrite)
  File "/usr/lib/python2.7/subprocess.py", line 1343, in _execute_child
    raise child_exception
OSError: [Errno 2] No such file or directory

What change do I need to make here?

  • 1
    By all appearances, the python script doesn't know where to find the executable named sqlcmd. Perhaps you just need the full path to it, or perhaps you're missing an environment file that would set up the PATH and any other variables that are needed. – Jeff Schaller Feb 8 '18 at 14:30
  • @jhallvid It doesn't seem that you assigned sqlcmd to anything. Just add sqlcmd = os.path.abspath('/your/path/to/file.sh') – dmb Feb 8 '18 at 14:39
  • Just a general note: Add colours and other knobs and gobbins after you have made sure that the active code is actually doing the correct things. It makes the code hard to read. – Kusalananda Feb 8 '18 at 14:47
  • 1
    Don't call sqlcmd. that's an sql injection attack waiting to happen. use whichever python library exists to allow direct connections and sql queries to your (unnamed) database server. python isn't sh, and while it can run external commands, you should only do that for things it can't do with a native python library (which is almost nothing) – cas Feb 8 '18 at 16:32
4

The Python script runs in an environment where the sqlcmd is not found in any of the directories that are listed in the PATH environment variable.

Make sure that PATH includes the directory where sqlcmd lives before invoking your script, or use sqlcmd with its full path.


I'm sure there are proper SQL libraries for Python that allows you to create database connections within the Python code without shelling out to some external binary. This would also allow you to do prepared statements which are less susceptible to SQL injection attacks

You never sanitize the UN variable, which means that one could call the script with "12345; DROP DATABASE 'mydatabase';"

  • Is there something I can put in my .bashrc to allow it to run every time? I have replaced the sqlcmd with the path /opt/mssql-tools/bin/sqlcmd but it spits out the help options as if I had put /opt/mssql-tools/bin/sqlcmd -? usage: sqlcmd [-U login id] [-P password] [-S server or Dsn if -D is provided] [-H hostname] – jhallvid Feb 9 '18 at 11:21
  • @jhallvid This just means that sqlcmd does not understand some of the options that you are giving it from the Python code. I'm unfamiliar with sqlcmd so I can't comment further on how you should or should not use this utility. Instead refer to the documentation for sqlcmd. – Kusalananda Feb 9 '18 at 11:24
  • @jhallvid Also, if you are at all able to, consider rewriting this script to use proper SQL connectors without shelling out to sqlcmd (as I and user cas suggested), and do something about that SQL injection vulnerability. – Kusalananda Feb 9 '18 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.