0

I am trying to query Aspen IP21 via the REST API using Python requests. My code works fine apart from when there is a LIKE statment in the SQL query which will have a % symbol in it. I have tried subsituting the % symbol with %25 but this seems incompatible with the API.

What is strange is when I copy and past the generated URL into the browser it works fine.

I intially tried:

url = '''https://server.com/ProcessData/AtProcessDataREST.dll/SQL?<SQL ds="host" dso="CHARINT=Y;CHARFLOAT=Y;CHARTIME=Y;CONVERTERRORS=N" t="SQLplus" m="1000000" to="60" s="1"><![CDATA[SELECT name FROM history WHERE name LIKE 'G7%']]></SQL>'''

requests.get(url,  auth=HTTPKerberosAuth()).json()

which returns:

{'data': [{'r': 'N'}]}

After searching around and suspecting it's because the percent symbol is being encoded I also tried

url = 'https://server.com/ProcessData/AtProcessDataREST.dll/SQL'
query = '''?<SQL ds="host" dso="CHARINT=Y;CHARFLOAT=Y;CHARTIME=Y;CONVERTERRORS=N" t="SQLplus" m="1000000" to="60" s="1"><![CDATA[SELECT name FROM history WHERE name LIKE 'G7%']]></SQL>'''

s = requests.Session()
req = requests.Request('GET', url,  auth=HTTPKerberosAuth())
p = req.prepare()
p.url += query
resp = s.send(p)

which also returns:

{'data': [{'r': 'N'}]}

If I then print(p.url) and paste into my browser it works fine.

Finally I tried subsituting the % symbol with %25

url = 'https://server.com/ProcessData/AtProcessDataREST.dll/SQL'
query = '''?<SQL ds="host" dso="CHARINT=Y;CHARFLOAT=Y;CHARTIME=Y;CONVERTERRORS=N" t="SQLplus" m="1000000" to="60" s="1"><![CDATA[SELECT name FROM history WHERE name LIKE 'G7%25']]></SQL>'''

s = requests.Session()
req = requests.Request('GET', url,  auth=HTTPKerberosAuth())
p = req.prepare()
p.url += query
resp = s.send(p)

But that still returns the same thing, but also makes the url pasted into the browser not work.

I think the python requests is encoding the % somehow which is then making the sql LIKE statement not find anything.

Lee G
  • 3
  • 3
  • `%` is used for URL encoding. A literal percent sign needs to be encoded like `%25` where 0x25 is the hexadecimal character code for the ASCII character `%`. – tripleee Apr 20 '23 at 11:54
  • Whoever specified this particular API should be taken behind the barn and spanked with a frying pan. – tripleee Apr 20 '23 at 11:55

1 Answers1

0

Several advice + an example that works for me.

it's always messy working for aspentech ip21 web api because you have to encapsulate sqlplus in CDATA in xml in URL... in python utf8 string. At least using POST remove the URL part, and also works with ip21 server <=10

It's never good to query pseudo-table (like history) without clause on time, but I guess you already know this and it's just an example.

import requests
import sys
from requests_negotiate_sspi import HttpNegotiateAuth

host = 'server.com'
url = 'http://'+host+'/ProcessData/AtProcessDataREST.dll/SQL'

payloadH = '<SQL c="DRIVER={AspenTech SQLplus};HOST=localhost;Port=10014;CHARINT=N;CHARFLOAT=N;CHARTIME=N;CONVERTERRORS=N" m="1000" s="1"><![CDATA['
payloadF =']]></SQL>'
payload = payloadH + "SELECT name FROM all_records WHERE name LIKE 'IP_%'" + payloadF

data = payload.encode('utf-8')

r = requests.post(url, auth=HttpNegotiateAuth(), data=data, timeout=60, verify=True)

print(r.content.decode('utf-8'))
Madgui
  • 405
  • 2
  • 10