The weirdest bug i ever faced

kakula · October 5, 2019, 7:32pm

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

rob · October 5, 2019, 10:27pm

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

kakula · October 5, 2019, 11:25pm

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

agramonte · October 6, 2019, 12:19am

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 )&nbsp;

agramonte · October 6, 2019, 12:31am

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

kakula · October 6, 2019, 12:32am

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

agramonte · October 6, 2019, 12:50am

I don’t know the answer to that.

rob · October 6, 2019, 1:16am

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

kakula · October 6, 2019, 11:28pm

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 %

richard11 · October 6, 2019, 11:46pm

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.

kakula · October 6, 2019, 11:58pm

@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

rob · October 7, 2019, 12:56am

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