Oracle invalid number in clause

Question

I'm struggling with getting a query to work, and I could really use some help. We have an in house app that we use for building small web apps where I work. It's basically a drag and drop GUI. There's functionality built in to access query string values using the key.

I'm passing a comma separated list of values into a page through the query string. I'm then trying to use the list of values as part of an in clause in a query.

I can see that the value is correct in the query string.

orders=1,2,3

Here's the specific part of the query

"AND OrderNumber IN (this is where it maps from the query string)

I've tried running similar queries in Toad, and I think I've found the issue. It's giving an invalid number error, and I think it's wrapping the query string value in single quotes. I can replicate the error when I do "AND OrderNumber IN ('1,2,3')" in Toad.

Here's where I get really confused. The following works in Toad. "AND OrderNumber IN ('1','2','3')"

So I tried recreating that by doing select replace('1,2,3', ',', chr(39)||','||chr(39)) from dual;

I have confirmed that returns '1','2','3' in Toad. However, I still get an Invalid Number error when I run the following in Toad.

AND OrderNumber IN (replace('1,2,3', ',', chr(39)||','||chr(39))

I've been racking my brain over this, and I can't figure it out. It seems to me that if "AND OrderNumber IN ('1','2','3')" works, and replace('1,2,3', ',', chr(39)||','||chr(39)) returns '1','2','3', that "AND OrderNumber IN (replace('1,2,3', ',', chr(39)||','||chr(39))" should work.

Any help you might be able to offer on this would be greatly appreciated. I know the rest of the query works. That's why I didn't post it. I'm just stuck on trying to get this IN clause working.

I guess when you have `AND OrderNumber IN ('1','2','3')`, Oracle is able to individually convert each string to a number to satisfy the condition. But a `'1,2,3'` string is not convertible to a number, so that fails. Makes sense to me. BTW, by the sounds of it, your in house app is very vulnerable to SQL injection attack. I hope it's not exposed publicly. — sstan, Jun 23 '16 at 03:15

score 2 · Answer 1 · edited May 23 '17 at 11:52

2

A change to phonetic_man's answer that will allow for NULL elements in the list. The regex format of '[^,]+' for parsing delimited lists does not handle NULL list elements and will return an incorrect value if one exists and thus its use should be avoided. Change the original by deleting the number 2 for instance and see the results. You will get a '3' in the 2nd element's position! Here's a way that handles the NULL and returns the correct value for the element:

SELECT TRIM(REGEXP_SUBSTR(str, '(.*?)(,|$)', 1, LEVEL, NULL, 1)) str
    FROM ( SELECT '1,,3,4' str FROM dual )
    connect by level <= regexp_count(str, ',') + 1;

See here for more info and proof: https://stackoverflow.com/a/31464699/2543416

edited May 23 '17 at 11:52

Community

1
1

answered Jun 23 '16 at 19:36

Gary_W

9,933
1
22
40

Thanks! I don't think I'll ever have to worry about working with nulls, but it's good to see info about how it works. – JoeBrewing Jun 23 '16 at 22:59
@Gary_W thanks for adding this. It is really important to manage NULLs. – phonetic_man Jun 24 '16 at 04:17
@JoeBrewing - Always expect the unexpected! – Gary_W Jun 24 '16 at 11:24

score 1 · Accepted Answer · answered Jun 23 '16 at 03:31

1

Can you try the following query.

SELECT * FROM orders
WHERE orderno IN    
    (
    SELECT TRIM(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) str
    FROM ( SELECT '1,2,3,4' str FROM dual )
    CONNECT BY INSTR(str, ',', 1, LEVEL - 1) > 0
    )

The inline query splitting the string in different rows. So, on executing it you will get the following result.

SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
    FROM ( SELECT '1,2,3,4' str FROM dual )
    CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0

1
2
3
4

Now, passing this result to the main query IN clause should work.

answered Jun 23 '16 at 03:31

phonetic_man

1,088
8
12

I will try this out. Thanks. – JoeBrewing Jun 23 '16 at 12:20
I tried this out and it works great. Thanks! Will you elaborate a little on what this does? I've been working on improving my SQL skills a lot lately, but I'm still not as strong as I would like. – JoeBrewing Jun 23 '16 at 12:35

score 0 · Answer 3 · answered Jun 23 '16 at 03:17

0

I think the desired clause to be built is:

AND OrderNumber IN (1,2,3)

A numeric list. The example you tested:

AND OrderNumber IN ('1','2','3')

works because an implicit conversion from a VARCHAR2 to a NUMBER is occurring for each element in the list.

The following clause will not work because no implicit conversion of the string '1,2,3' can be made (note the clause has a single string element):

AND OrderNumber IN ('1,2,3')

When doing a replace, you are converting the single string: 1,2,3 with the single string: 1','2','3 and this single string cannot be implicitly converted to a number.

answered Jun 23 '16 at 03:17

Glenn

8,932
2
41
54

That's why I added char(39)|| before and ||char(39) after the replace, to add the missing single quotes from the beginning and end. That still doesn't seem to be good enough for what I need though. – JoeBrewing Jun 23 '16 at 12:19
@JoeBrewing But that is only embedding quotes into the existing string rather than creating new strings (unless you are using dynamic sql). – Glenn Jun 23 '16 at 12:37
I see. That makes sense. Thanks for the clarification. – JoeBrewing Jun 23 '16 at 13:25

Oracle invalid number in clause

3 Answers3

Linked