Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

The weirdest bug i ever faced
Started by kakula Oct 05 2019 11:32 AM

11 replies to this topic
sql search webservice bug
[TOPIC CONTROLS]
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

kakula

[GLOBAL: userInfoPane.html]
kakula
  • Contributor

  • 103 posts
  • Corona SDK

I'm facing a very weird bug... i have to report it!

 

i have an app with a search screen

 

when you type in the search box, the program passes a search query to a webservice

 

everything is working perfectly and smoothly

 

i have one item in the database written exactly as follows

 

BALADNA YELLOW CHEESE 450 GM

 

When you search for this item using any word except for the word BALADNA .... the item is returned

whether you are using SQL directly, or using the webservice directly, or using corona through network.request

 

if you search for the word BALADNA directly from SQL it is working

if you search for the word BALADNA directly from the webservice it is working

 

if you search for the word BALADNA directly from app which calls the same webservice it is not working

 

 

no special or hidden characters .... i did notepad typing to make sure ... and after all the same item or any other item is returned through corona .... and if i search for this item from corona using the word yellow it is working but if i search for BALADNA it is not (lower case upper case ... nothing is working)

 

This is the webservice

 

http://onestopshop-001-site1.atempurl.com/webservice1.asmx?op=GeneralSQL

 

 if you type this in the webservice it will work

 

SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%yellow%b%' Or Item_NameE Like '%yellow%b%') Order By Item_NameA23655485478

 

call it from corona it will work!!! with this event.response

 

<?xml version="1.0" encoding="utf-8"?>

<string xmlns="http://tempuri.org/">[{"Cat_ID":"72","Item_ID":"10","Item_Name":"YELLOW CHEESE 450 GM Baladna","Unit_Price":"2.5"}]</string>
 

 

if you type this ... it will also work

 

SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%BALADNA%' Or Item_NameE Like '%BALADNA%') Order By Item_NameA23655485478

 

call it from corona it will not work!!! with this event.response

 

<?xml version="1.0" encoding="utf-8"?>

 
 

 

i have done tons of sql database apps and systems ... i never faced anything like this

 

 

 

Very Important Note:

Seems this is officially a bug!!

I just tried this in another app i made (https://play.google.com/store/apps/details?id=com.gmail.Fruits.MyAds)  ... i entered an Ad with the word baladna as the subject and the body of the Ad... it is there ... when searched for it ... it is not appearing although searching for anything else is working

Attached Files



[TOPIC: post.html]
#2

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 26,207 posts
  • Enterprise

Can you post your code leading up to, and including your network.request() call? Please use the blue <> code formatting button and paste your code into the popup box.  

 

Also we don't know much about your database. In general LIKE "%something%" is case sensitive unless your collation is set up to be case insensitive. In the database you have "Baladna", but your searches are either all lower case or all upper case.

 

Most people are doing some upper case or lower case conversion to avoid case sensitivity problems. Here is a stack overflow article on it:

 

https://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard

 

I don't know if your web tool that you're using is hacking on the string where perhaps network.request() is not.

 

Rob



[TOPIC: post.html]
#3

kakula

[GLOBAL: userInfoPane.html]
kakula
  • Contributor

  • 103 posts
  • Corona SDK

Hi Rob,

 

I'm using MS SQL database with a collation that is case insensitive

 

if you search for Yellow, YELLOW, yellow it's all the same

same should go for Baladna, BALADNA, baladna ...etc....

 

and don't forget the same code is working perfectly to search for anything ... whether Arabic or English characters ... small case or lower case

 

and also don't forget that searching for the word baladna inside MSSQL directly or through the webservice is also working perfectly

 

and finally remember that the same problem is happening with another app, using a different database and a different webservice

 

i reached a point where i believe that lua has something against the word baladna in particular ... or part of it :-) ... might be some kind of a reserved word

 

you can test it from yourside ... if you have any app that searches a database

 

i will attache the whole source code ... the file named search.lua is having the problem .... just search for the word print ... you will find 2 of them ... one to print the sql command being passed, and one to print the response .... just try searching for anything above or equal to 6 characters like the word yellow or baladna .... the funny thing both words are in the exact same field in the same record ... so there couldn't be a possibility for corrupt returned json string

 

when you start the app just hit the search button upper right of the screen and start searching

 

google drive link for the source code folder

 

https://drive.google.com/file/d/1862halFxwjwWOzkwBSvFPu4Aw4HvqHb9/view?usp=sharing

 

 



[TOPIC: post.html]
#4

agramonte

[GLOBAL: userInfoPane.html]
agramonte
  • Corona Geek

  • 1,245 posts
  • Corona SDK

Procrastination is powerful tool for helping others. I can confirm everything you said. Further more I can confirm that if I use postman both queries work as intended.

 

Although I couldn't figure out why it is doing it. I can tell you that somehow %BA is being interpreted as a special character. You have a much bigger problem that just BALADNA. Any item name that starts with BA will have the same problem. For example I noticed that you have a product called: NEWLAND BALSAMIC. You won't find it if you search for "BALSAMIC" but you will get it back if you search for "%ALSAMIC".

 

This will fail:

local notWorking = "QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,
Item_NameA As Item_Name,Unit_Price FROM ItemsV 
Where Item_NameA Is Not Null 
And (Item_NameA Like '%BALADNA%' 
Or Item_NameE like '%BALADNA%') Order By Item_NameA23655485478"

This will not:

local notWorking = "QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,
Item_NameA As Item_Name,Unit_Price FROM ItemsV 
Where Item_NameA Is Not Null 
And (Item_NameA Like '%BALADNA%' 
Or Item_NameE like '%ALADNA%') Order By Item_NameA23655485478"

I made a much simpler reproducible code example (it might help Rob):

-----------------------------------------------------------------------------------------
--
-- main.lua
--
-----------------------------------------------------------------------------------------

-- Your code here
local json = require("json")

local headers = {}
headers["accept"] = "application/json";
headers["Content-Type"] = "application/x-www-form-urlencoded";
headers["Accept-Language"] = "en-US";
headers["User-Agent"] = "zzzz";

local notWorking = "QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%BALADNA%' Or Item_NameE like '%BALADNA%') Order By Item_NameA23655485478"

local Working = "QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%yellow%b%' Or Item_NameE Like '%yellow%b%') Order By Item_NameA23655485478"

local params = {}
params.headers = headers
params.body = notWorking

local requestListerner = function(event) 
    print(json.prettify( event ))

end


network.request( "http://onestopshop-001-site1.atempurl.com/webservice1.asmx/GeneralSQL", "POST", requestListerner, params ) 


[TOPIC: post.html]
#5

agramonte

[GLOBAL: userInfoPane.html]
agramonte
  • Corona Geek

  • 1,245 posts
  • Corona SDK

Some more from Charles.

 

Failed request coming from simulator:

 

POST /webservice1.asmx/GeneralSQL HTTP/1.1

Host: onestopshop-001-site1.atempurl.com
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Connection: keep-alive
Accept: application/json
User-Agent: zzzz
Accept-Language: en-US
Content-Length: 215
Accept-Encoding: gzip, deflate
 
QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%BALSAMIC%' Or Item_NameE like '%BALSAMIC%') Order By Item_NameA23655485478

 

Working request:

 

POST /webservice1.asmx/GeneralSQL HTTP/1.1

Host: onestopshop-001-site1.atempurl.com
Content-Type: application/x-www-form-urlencoded; charset=UTF-8
Connection: keep-alive
Accept: application/json
User-Agent: zzzz
Accept-Language: en-US
Content-Length: 214
Accept-Encoding: gzip, deflate
 
QueryValueS1=SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%BALSAMIC%' Or Item_NameE like '%ALSAMIC%') Order By Item_NameA23655485478


[TOPIC: post.html]
#6

kakula

[GLOBAL: userInfoPane.html]
kakula
  • Contributor

  • 103 posts
  • Corona SDK

Great analysis agramonte

 

i can handle this by code .... if the search string had BA then remove the B ... but this is not practical ... is this something from Corona's side to fix



[TOPIC: post.html]
#7

agramonte

[GLOBAL: userInfoPane.html]
agramonte
  • Corona Geek

  • 1,245 posts
  • Corona SDK

I don't know the answer to that.



[TOPIC: post.html]
#8

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 26,207 posts
  • Enterprise

First, thanks to @agramonte's simpler example, this also happens on the Mac simulator. I went ahead and filed a bug report along with a sample project that demonstrates the issue. However, given the other things we are working on/bugs we are fixing, this may take a while to get looked at.

 

I would suggest that if someone wants to see if they can find the problem, here is the path to the open source version of the plugin: 

 

https://github.com/coronalabs/submodule-plugins-network/tree/23daa8e4b3cca9680d3d5b869cbff19c2d1037f1

 

I didn't see anything obvious in the Lua side. Also it would be helpful to have this tested on a couple of devices (Android, iOS) to see if it's systemic to all platforms, or limited to macOS (and Windows I assume from the screenshots).

 

Rob



[TOPIC: post.html]
#9

kakula

[GLOBAL: userInfoPane.html]
kakula
  • Contributor

  • 103 posts
  • Corona SDK

Just to report the full bug ... which seems to be severe and deserves a high priority

 

 

if you search for one letter ... everything works fine

 

like %a%, %b%, %c%,.....%z%

 

however if you search for 

 

%a?,%b?,%c?,%d?,%e?

 

where ? is any character from a to z .... search crashes

 

and sometimes it crashes with other combinations like searching for %fa crashes ... but %fg does not

 

i think there is something related to string replacement for some kind of reserved words ...

 

Is there a possibility to test this with another app that uses database search created by someone other than me ... using another web service ... like a php Web service

 

a quick but not good solution is to insert % between all letters 

so if you are searching for %cheese ... it should be %c%h%e%e%s%e ... this would work ... but result list will bring unwanted items due to the fuzzy search created by % 



[TOPIC: post.html]
#10

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 475 posts
  • Corona SDK

I'm only loosely skimming over the other posts here, so this may not be useful, but ref the findings where %alsamic% works and %balsamic% doesn't - I've found in the past that some database engines treat % as "1 or more characters" where others treat it as "zero or more". Could this be the case here?

Where this has happened to me before, I've just done it this way...

WHERE (fldName = 'balsamic' OR fldName LIKE 'balsamic%' OR fldName LIKE '%balsamic%' OR fldName LIKE '%balsamic')

Not ideal I know, but it works.

[TOPIC: post.html]
#11

kakula

[GLOBAL: userInfoPane.html]
kakula
  • Contributor

  • 103 posts
  • Corona SDK

@richard11

 

Hi Richard,

 

The real problem is that the query works fine if you run it directly from the database manager ... exact and precise

and it works fine if you pass the proper query with parameters to the webservice

but if you pass the same parameters to the webservice through Corona using netwrok.request ... it returns nothing ... although the webservice is working perfectly with corona if you search for anything else (actually anything that does not use what i mentioned above)

 

I think what is happening exactly is that when corona passes the url to the webservice ... it makes some changes which is causing the problem ... because corona does not really care what % is interpreted by database engines, because eventually it is not talking to the database ... it is only talking to the webservice which is talking to the database ... and the webservice has no problems and working fine



[TOPIC: post.html]
#12

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 26,207 posts
  • Enterprise

I did a little research tonight because this problem is "bug"ing me. I don't believe this to be a bug at all but simply how HTTP POST works. The header is showing: 

Content-Type: application/x-www-form-urlencoded; charset=UTF-8

This, if I read everything correctly, means you need to URL Encode the post data. The server is expecting the text to be encoded and the %'s trigger the server to decode the data converting the % and the next two bytes from a hex value to the appropriate character. 

 

Forms sent from web browsers URL encode under the hood. The user should never have to worry about this. However Corona is giving you RAW access to the request which means you're responsible for your own URL encoding.

 

Try encoding it like this:

local shouldWork = "QueryValueS1=" .. urlencode("SELECT Top 50 Cat_ID,Item_ID,Item_NameA As Item_Name,Unit_Price FROM ItemsV Where Item_NameA Is Not Null And (Item_NameA Like '%BALADNA%' Or Item_NameE like '%BALADNA%') Order By Item_NameA23655485478")

local params = {}
params.headers = headers
params.body = shouldWork


You will have to find your own URL encoding function, as we don't have a stock one.

 

Rob




[topic_controls]
[/topic_controls]

Also tagged with one or more of these keywords: sql, search, webservice, bug