Bug 1252046 - API: Numerical values exceeding Integer raise Integer error on retrieving data
API: Numerical values exceeding Integer raise Integer error on retrieving data
Status: CLOSED DEFERRED
Product: Red Hat Satellite 6
Classification: Red Hat
Component: Discovery Plugin (Show other bugs)
6.1.0
Unspecified Unspecified
unspecified Severity medium (vote)
: Unspecified
: --
Assigned To: satellite6-bugs
Roman Plevka
: Triaged
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2015-08-10 10:33 EDT by Roman Plevka
Modified: 2017-03-20 12:19 EDT (History)
5 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2017-03-20 12:19:39 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Screenshot with issue on UI (61.86 KB, image/png)
2015-08-10 11:14 EDT, Oleksandr Shtaier
no flags Details


External Trackers
Tracker ID Priority Status Summary Last Updated
Foreman Issue Tracker 9088 None None None Never

  None (edit)
Description Roman Plevka 2015-08-10 10:33:34 EDT
Description of problem:
Integer errors are being raised on putting large numerical values (>2147483647)
as values for numerical id attributes:
e.g.:
organization-id,
user-id,
environment-id, etc.

Version-Release number of selected component (if applicable):


How reproducible:
every time:
# working example with max integer value:
https://<fqdn>/katello/api/v2/gpg_keys?organization_id=19&search=2147483647

{"total":2,"subtotal":0,"page":1,"per_page":20,"search":"2147483647","sort":{"by":null,"order":null},"results":[]}

# non working example with max_integer+1 value:
https://<fqdn>/katello/api/v2/gpg_keys?organization_id=19&search=2147483648

{"displayMessage":"PGError: ERROR:  value \"2147483648\" is out of range for type integer\nLINE 1: ...83648%' OR \"katello_gpg_keys\".\"organization_id\" = '214748364...\n                                                             ^\n: SELECT COUNT(*) FROM \"katello_gpg_keys\"  WHERE \"katello_gpg_keys\".\"id\" IN (SELECT DISTINCT \"katello_gpg_keys\".\"id\" FROM \"katello_gpg_keys\"  WHERE \"katello_gpg_keys\".\"organization_id\" = 19) AND ((\"katello_gpg_keys\".\"name\" ILIKE '%2147483648%' OR \"katello_gpg_keys\".\"organization_id\" = '2147483648'))","errors":["PGError: ERROR:  value \"2147483648\" is out of range for type integer\nLINE 1: ...83648%' OR \"katello_gpg_keys\".\"organization_id\" = '214748364...\n                                                             ^\n: SELECT COUNT(*) FROM \"katello_gpg_keys\"  WHERE \"katello_gpg_keys\".\"id\" IN (SELECT DISTINCT \"katello_gpg_keys\".\"id\" FROM \"katello_gpg_keys\"  WHERE \"katello_gpg_keys\".\"organization_id\" = 19) AND ((\"katello_gpg_keys\".\"name\" ILIKE '%2147483648%' OR \"katello_gpg_keys\".\"organization_id\" = '2147483648'))"]}


Steps to Reproduce:
1. take any attribute referable via integer
2. place a request to retrieve such entity (see example above)
3. get the http 500 status with DB error.

Actual results:
HTTP 500 received with DB error

Expected results:
Input validation should take care of the posted values the correct way.

Note the issue is very visible in the UI (search field).
In some cases the error page is received, but in most of the cases the search is being executed in the form of an AJAX call,
so it fails silently.

related bugs:
https://bugzilla.redhat.com/show_bug.cgi?id=1192356

Additional info:
Comment 1 Oleksandr Shtaier 2015-08-10 11:14:04 EDT
Of course, as a side effect, that issue can be reproduced from WebUI and can be pretty critical as we have impact on a lot of different entities. And, it fails badly like on attached screenshot
Comment 2 Oleksandr Shtaier 2015-08-10 11:14:35 EDT
Created attachment 1061139 [details]
Screenshot with issue on UI
Comment 3 sthirugn@redhat.com 2015-08-10 11:22:01 EDT
(In reply to Oleksandr Shtaier from comment #1)
> Of course, as a side effect, that issue can be reproduced from WebUI and can
> be pretty critical as we have impact on a lot of different entities. And, it
> fails badly like on attached screenshot

Workaround for UI issue is to use the attribute name. The following works for me:
name =  12345678901234567890123456789012345678901234567890123456789012345678901234567890
Comment 5 sthirugn@redhat.com 2015-08-10 11:28:37 EDT
exact failure scenario in UI:

1) Use a numeric attribute name >= 2147483648
2) from UI search for this value without providing its name `name =`
Comment 6 Jan Hutař 2015-08-10 15:51:06 EDT
What is wrong here is that failed SQL is returned in the error message as it might reveal some details which should be left hidden - but I do not have any proof here - this is IMO just a generic rule. It should be logged only.
Comment 7 Roman Plevka 2015-08-12 04:35:24 EDT
@Jan_Hutar - Comment #6:
I don't think 'hiding the message' is the answer. The error leaves UI in the error state - e.g. infinite `loading spinner`, etc.
I believe the true fix would involve validating the input before it gets to the DB query (something that would simply leaves out looking for integer-based id's in case the value exceeds integer boundaries)
Comment 8 Jan Hutař 2015-08-24 06:54:04 EDT
(In reply to Roman Plevka from comment #7)
> @Jan_Hutar - Comment #6:
> I don't think 'hiding the message' is the answer.

Yep, that was not meant as a fix, just as something which shold happen anyway.
Comment 9 Bryan Kearney 2016-04-27 15:12:17 EDT
Upstream bug component is Discovery Plugin
Comment 10 Bryan Kearney 2016-04-27 15:12:19 EDT
Moving to POST since upstream bug http://projects.theforeman.org/issues/9088 has been closed
Comment 12 Roman Plevka 2016-10-14 08:49:23 EDT
FAILED_QA
sat6.3 snap3

# curl -ku admin:changeme "https://$(hostname)/api/locations?search=999999999999999999999999999999"
{
  "error": {"message":"PG::Error: ERROR:  value \"999999999999999999999999999999\" is out of range for type integer\nLINE 1: ...999999999999999999999999%' OR \"taxonomies\".\"id\" = '999999999...\n                                                             ^\n: SELECT  \"taxonomies\".* FROM \"taxonomies\" WHERE \"taxonomies\".\"type\" IN ('Location') AND ((\"taxonomies\".\"title\" ILIKE '%999999999999999999999999999999%' OR \"taxonomies\".\"name\" ILIKE '%999999999999999999999999999999%' OR \"taxonomies\".\"id\" = '999999999999999999999999999999' OR \"taxonomies\".\"label\" ILIKE '%999999999999999999999999999999%'))  ORDER BY \"taxonomies\".\"title\" ASC NULLS FIRST  LIMIT 20 OFFSET 0"}
}


please note, that the purpose of this bugzilla was to point at faulty (or missing) input validation for large numbers *in general*, not just the gpg_keys endpoint (that one has been picked as an example).
Comment 13 Bryan Kearney 2017-03-20 12:19:39 EDT
This bug has an upstream issue. When this issue is resolved, it will be included in the next Satellite release. We will no longer be tracking this downstream. If you feel this should not have been closed, please feel free to re-open with additional details.

Note You need to log in before you can comment on or make changes to this bug.