HTTP_GET and HTTP_GET_BLOB

Edit online

The HTTP_GET Start of change or HTTP_GET_BLOB End of change scalar function retrieves a text-based resource from the specified URL through an HTTP GET request.

url: An expression that returns a built-in character string or graphic string data type that specifies the URL of the resource being accessed.; The only supported protocols in the URL are http:// and https://. HTTP basic authentication in the form https://userid:password@ cannot be specified in the URL. Instead, the basicAuth option must be used to specify the userid and password for basic authentication.
options: An expression that returns a built-in character string or graphic string data type. This string must be formatted as a JSON object containing the options, including headers, for the request. See HTTP options for the list of options.; If no options are provided, the default options are used.

The result of the HTTP_GET function is CLOB(2G) CCSID 1208 containing the response message. Start of change The result of the HTTP_GET_BLOB function is BLOB(2G) containing the response message. End of change

Notes

For more information about using the HTTP functions see HTTP functions overview.

To return the header information from the HTTP request, use the HTTP_GET_VERBOSE Start of change or HTTP_GET_BLOB_VERBOSE End of change table function: HTTP_GET_VERBOSE and HTTP_GET_BLOB_VERBOSE

Example

Retrieve service information from the IBM® PSP website using a certificate store created from a Java™ certificate store. See SSL considerations for more information.

VALUES QSYS2.HTTP_GET(
             'https://www.ibm.com/support/pages/sites/default/files/inline-files/xmldoc.xml',
             '{"sslCertificateStoreFile":"/home/javaTrustStore/fromJava.KDB"}');

HTTP options

Each HTTP function has a parameter for passing options to be used on the request. These options include the setting of the HTTP headers. This string must be a JSON object in the following format:

{"option1":"option-setting1","option2":"option-setting2"}

For example, using the option string Start of change

{"headers":{"User-Agent":"IBM i HTTP function"}}

will cause

"User-Agent": "IBM i HTTP function"

to be included in the HTTP header sent to the server.

The following options can be set in the JSON object:

Table 1. HTTP options
Option	Possible settings	Default setting	Description
basicAuth	userid,password		Sets the userid and password that will be used for HTTP basic authentication. To prevent exposure of the userid and password, this option is only allowed when the URL begins with https:.
connectTimeout	0-2000000	Dependent on TCP/IP system settings	Sets the connect timeout value. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a connection to complete.
header	headername,headervalue		Sets an HTTP header in the HTTP request with the specified headername and headervalue. This option can be specified multiple times to set multiple headers. It is recommended that the headers option be used instead of the header option.
headers	{"header1":"value1", "header2":"value2", "header3":"value3" }		Sets one or many HTTP headers in the HTTP request. The setting consists of a JSON object with key value pairs representing a header name and corresponding header value. A common header that may be required is the Accept header. This header can be set in the following way: `{"headers":{"Accept":"*"}}` By default, only the Host and User-Agent HTTP header are set for all requests. The Host is set using the host information in the URL. The User-Agent header defaults to `curl/XX.YY` where XX.YY are some version numbers. This value was chosen to provide anonymity when accessing web services. If the content-type HTTP header is not set for a POST or PUT operation, the default content type that will be used is: `Content-Type: text/xml; charset=UTF-8` To correctly use these headers, consult the appropriate Web server and RFC documentation on the use of HTTP request headers. Examples of RFCs are RFC2616 and RFC7231.
ioTimeout	0-2000000	Dependent on TCP/IP system settings	Sets how long to wait for a read request to complete. If timeout is greater than zero, the value will be used as the maximum time, in seconds, to wait for a read request to complete.
proxy	host,port		Sets the HTTP proxy information.
proxyAuth	userid,password		Sets the HTTP proxy authentication information. To prevent exposure of the userid and password, this option is only allowed when the `proxySsl` option is true.
proxySsl	true false	true	Sets whether a secure (SSL) connection should be used when connecting to the proxy server. A value of true indicates that the transport will connect to the proxy server using a secure channel. A value of false indicates that the transport will connect to the proxy server using an unsecure channel.
redirect	0-2000000	0	Specifies the number of HTTP redirects that will be followed. If the number is greater than zero, HTTP redirects will be followed up to the number specified. If the value is less than one, HTTP redirects will not be followed.
signalErrors	true false	false	Indicates whether an HTTP error should be signaled as an SQLException. This will be seen as SQLSTATE 38501, SQLCODE -443.
sslCertificateStoreFile	file	/QIBM/USERDATA/ICSS/ CERT/SERVER/DEFAULT.KDB	Specifies the name of the certificate store file to be used for the secure session or SSL environment. This parameter is ignored if the `sslApplicationID` option is set to a value. If SSL communication is to be done by using a path to a keystore file, the user profile the application is running under must have authority to the file. Execute (X) data authority to each directory preceding the stream file being read and Read (R) data authority to the stream file This option is only used when the URL begins with https:.
sslCertificateStorePassword	password		Specifies the password for the certificate store file to be used for the secure session or SSL environment. If the option is not passed or is set to the null string, the internal stash file associated with the user profile that is being used to run the application is used as long as the user has authority to the certificate store file and the password has been used one time on the system. This option is ignored if the `sslApplicationID` option is set to a value. This option is only used when the URL begins with https:.
sslCertificateLabel	label		Specifies the label associated with the certificate in the certificate store to be used for the secure session or SSL environment. If the option is not passed or is set to the null string, the default certificate label in the specified certificate store file is used for the SSL environment. This option is ignored if the `sslApplicationID` option is set to a value. This option is only used when the URL begins with https:.
sslTLS11	ENABLE NONE	NONE	Enable or disable the TLS Version 1.1 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:.
sslTLS12	ENABLE NONE	ENABLE	Enable or disable the TLS Version 1.2 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:.
sslTLS13	ENABLE NONE	ENABLE	Enable or disable the TLS Version 1.3 ciphers. A value of NONE will disable the ciphers; any other value will enable the ciphers. This option is only used when the URL begins with https:.
sslTolerate	true false	false	Tolerate soft validation errors (expired certificate or certificate not in certificate store). Specify a value of true to tolerate soft validation errors, or false to not tolerate soft validation errors. Warning: Enabling this option allows man-in-the-middle attacks to occur and is not recommended. This option is only used when the URL begins with https:.
sslApplicationId	id		Application ID to use for the SSL environment. This option is only used when the URL begins with https:.
sslDomainName	name	Set using hostname present in https: URL	Fully qualified domain name that will be used as Server Name Indication (SNI) as defined by RFC 6066. This option is only used when the URL begins with https:.
sslSniCritical	true false	false	A value of true indicates that the SNI request is critical and thus the server must support the SNI extension. Otherwise, the secure connection will fail if server does not send the extension. A value of false indicates that the server does not need to support the SNI extension. This option is only used when the URL begins with https:.
verboseResponseHeaderFormat	json jsonCookiesArray jsonCookiesObject jsonString raw	json	Specifies the formatting used by the RESPONSE_HTTP_HEADER column of the HTTP_xxx_VERBOSE functions. json If the header value is a valid JSON value, it is used directly as the JSON value. If it is not a valid JSON value, it is converted to a JSON string. With this setting, any JSON object returned as a JSON header can be accessed as a JSON value within the constructed RESPONSE_HTTP_HEADER JSON object. Similarly, JSON numbers and special values can be accessed as numbers and special values. The disadvantage is that the outer quotes surrounding a header value are not preserved, as this is interpreted as a JSON string. Cookies will be returned with the Set-Cookie key. For example, a response containing cookies named cookie1 and cookie2 would look like the following within the JSON object: `"Set-Cookie":"cookie1=cookie1value", "Set-Cookie":"cookie2=cookie2value"` jsonCookiesArray The format is like the `json` option, but the cookies are returned as objects within a JSON array. For example, the response containing cookies named cookie1 and cookie2 would look like the following: `"Set-Cookies": [{"cookie":"cookie1", "value":"cookie1value"}, {"cookie":"cookie2", "value":"cookie2value"}]` jsonCookiesObject The header values are formatted like the `json` option, but cookies are returned within a JSON object with a Set-Cookies key. The object has the cookie as the JSON key and the value of the cookie as the JSON value. For example, a response containing cookies named cookie1 and cookie2 would look like the following: `"Set-Cookies": {"cookie1":"cookie1value", "cookie2":"cookie2value"}` jsonString The header value is always converted to a JSON string. If the header value contains quotes, they will be escaped within the JSON string. raw The unformatted response message and headers are returned.

Some of the options can expose sensitive information such as passwords. A number of best practices can be use to prevent the sensitive information from being exposed in the SQL statement text, which may be visible using DBMON. Here are some examples of best practices to protect the password.

Use a global variable and concatenate the global values when the SQL Statement is executed.

CREATE VARIABLE MYLIB.HTTP_PW VARCHAR(30); 
SET MYLIB.HTTP_PW = 'http_pwd';
VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', '{"basicAuth":"userid,' CONCAT MYLIB.HTTP_PW CONCAT '"}' );

For interfaces that support a parameter marker, set the password using a parameter marker. Here is an QSHELL example using the JDBC client provided in jt400.jar.

java -jar /qibm/proddata/os400/jt400/lib/java6/jt400.jar jdbc:db2:localhost
!PREPARE select QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', 
                                '{"basicAuth":"userid,' CONCAT ? CONCAT '"}' ) from sysibm.sysdummy1
!setParm 1,http_pwd
!executeQuery

Store passwords in a table, protected with a column mask.

CREATE MYLIB.TABLE PASSWORDS (TYPE VARCHAR(20), PWD_VALUE VARCHAR(100) CCSID 37);

CREATE MASK MYLIB.PASSWORD_MASK ON MYLIB.PASSWORDS
FOR COLUMN PWD_VALUE RETURN
CASE WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,'<my user id>') = 1)
THEN PWD_VALUE
ELSE 'Sorry' END
ENABLE;

ALTER TABLE MYLIB.PASSWORDS
ACTIVATE COLUMN ACCESS CONTROL;

INSERT INTO MYLIB.PASSWORDS VALUES ('HTTP', 'My secure password');

VALUES QSYS2.HTTP_GET( 'https://www.somesite.com/authorized_info.html', 
                       '{"basicAuth":"userid,' CONCAT 
                       (SELECT PWD_VALUE FROM MYLIB.PASSWORDS WHERE TYPE = 'HTTP') CONCAT 
                       '"}' );