db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Call statement with an SQL request as an input parameter
Date Tue, 30 Apr 2013 16:28:12 GMT
On 4/30/13 8:51 AM, Guillaume CHAUVET wrote:
>
> Hi,
>
> Below, please find a small SQL script intended to inject a value 
> selected from an SQL request as a input call statement parameter :
>
> *SELECT center FROM APP.SETTINGS WHERE ID = 1 *
>
> >Return a varchar(128) string, for example: ‘test’
>
> Now, I try to use this value as a input parameter in a call statement :
>
> *CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('center', (SELECT center 
> FROM APP.SETTINGS WHERE ID = 1)); *
>
> />Error code -1, SQL state XJ001: DERBY SQL error: SQLCODE: -1, 
> SQLSTATE: XJ001, SQLERRMC: java.lang.NullPointerException XJ001.U /
>
> />Line 1, column 1 /
>
> />[This SQL request reset the JDBC connection] /
>
> According to you, is it the expected behaviour or a bug ?
>
> -- 
>
> Regards,
>
> Guillaume
>
Hi Guillaume,

Both arguments to that system procedure should be strings. In your 
example, the second argument is not enclosed in single quotes, so the 
parser does not interpret it as a string. That's why you get the error 
you are seeing.

But it sounds as though you don't want to bind that SELECT string as the 
value of the "center" key. Instead, what you want to bind to "center" is 
the result of running that SELECT. You will need to run 2 statements:

1) Run the SELECT and retrieve its string result.

2) In a separate statement, bind that result to the "center" key by 
calling the system procedure.

Hope this helps,
-Rick

Mime
View raw message