Sanitization

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Sanitization

Post by MaxV » Mon Oct 30, 2017 10:58 am

Hi,
few days ago I made a presentation of Livecode on a public audience. The majority of people asked me what SQL sanitization does livecode.
Do yo know some libraries that I could converto to Livecode for SQL sanitization?
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Sanitization

Post by AxWald » Mon Oct 30, 2017 12:02 pm

Hi,
attached a stack to play with. It's not a 'real SQL Sanitation', but it helps a lot for getting unwanted chars removed/ replaced from your input.

It's a tool to setup 2 arrays ("Replace array" & "Kill array") that are later used for replacement/ killing potentially harmful chars from an input string:
  1. Replacing: 2 keys, each with a bunch of tab-separated ASCII numbers.
    Each (Item i of ReplArray[1]) in myInput will be replaced with (Item i of ReplArray[2])
    Examples: " -> ´ or ; -> .
  2. Killing: 1 Key, a bunch of tab-separated ASCII numbers
    (defined as ranges in the UI).
    After replacing, each item of KillArray[1] in myInput will be replaced with empty.
    Examples of unwanted chars: % *
The tool comes predefined with a rather restrictive preset that can easily be changed (dbl-click the tables ...). Tools for testing & moving the arrays to a target stack are present.
The actual workhorse function is rather fast & easy to use:

Code: Select all

UPDATE tTable SET fName='" & sanText(myInput) & "' WHERE ...
GPL v3, works with LC 6.7.10. Have fun!
Attachments
awa_SimpleSan.zip
a SimpleSan tool
(4.93 KiB) Downloaded 238 times
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Sanitization

Post by Mikey » Mon Oct 30, 2017 2:46 pm

Look up parameter queries and don't try to sanitize the input. It is very, very hard to get input sanitized so that you can both stop bad input and allow the user to enter any appropriate input. If you use query parameters, sql-injection doesn't happen.

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Sanitization

Post by MaxV » Mon Oct 30, 2017 6:10 pm

Mikey wrote:
Mon Oct 30, 2017 2:46 pm
Look up parameter queries and don't try to sanitize the input. It is very, very hard to get input sanitized so that you can both stop bad input and allow the user to enter any appropriate input. If you use query parameters, sql-injection doesn't happen.
How do you use parameter queries?
I knew that parametrization is a false myth, it doesn't change anything, look this:

Code: Select all

put "SELECT * FROM bugs WHERE id='" & tSQL & "'" into mySQL
If tSQL is: 2' or '1'='1
you get all the rows.
Last edited by MaxV on Mon Oct 30, 2017 6:31 pm, edited 1 time in total.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Sanitization

Post by MaxV » Mon Oct 30, 2017 6:28 pm

is base64encode enough?
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Sanitization

Post by Mikey » Mon Oct 30, 2017 6:41 pm

No. What you are doing is exactly what leads to injection. Trying to "sanitize" the input, i.e. prevent injection while allowing the user the freedom to enter the data that they need to enter is very difficult. The code to do it is complex - you in effect would have to write your own parser to catch every case.

Parameterization means you are substituting SQL parameters (placeholders) for the values:

Code: Select all

SELECT * FROM bugs WHERE id=:1
Look at revDataFromQuery in the dictionary. The third example shows the use of parameterization. You populate the variables/arrays with the values you want to pass to the query, and use the placeholders in the query text.

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Sanitization

Post by MaxV » Tue Oct 31, 2017 11:40 am

Wow, I made some tests and you are right!!!! Great!!!
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Sanitization

Post by AxWald » Wed Nov 01, 2017 5:12 pm

Hi,
Mikey wrote:
Mon Oct 30, 2017 6:41 pm

Code: Select all

SELECT * FROM bugs WHERE id=:1
Is there any documentation what this actually does? In the end there's a SQL string that is sent to the db, sure. Means, I know how to use the "id=:1" syntax - I just don't know what magik LC does to justify a statement like:
If you use query parameters, sql-injection doesn't happen.
What may happen in any case is input that causes errors later. If I need a 1-line string, it should contain no line feeds, and maybe should be of a certain length. If the db expects an integer, the input should be one - so input values checking is a MUST anyways.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Sanitization

Post by Mikey » Wed Nov 01, 2017 5:32 pm

I would suggest getting on The Google and reading up on sql injection and sql parameters. That will help you get your brain wrapped around it. AFA as type-checking, that obviously depends on the data type you are querying.

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Sanitization

Post by MaxV » Thu Nov 02, 2017 2:49 pm

AxWald wrote:
Wed Nov 01, 2017 5:12 pm
Hi,
Mikey wrote:
Mon Oct 30, 2017 6:41 pm

Code: Select all

SELECT * FROM bugs WHERE id=:1
Is there any documentation what this actually does?
I made some research and usually SQL parametrization is that the query is sent as a query, and the database knows exactly what this query will do, and only then will it insert the data merely as values. This means they cannot effect the query, because the database already knows what the query will do.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Sanitization

Post by AxWald » Mon Nov 06, 2017 1:28 pm

Hi,

I may have been too vague in my question. What I wanted to know was:

What does LC make with this statement:

Code: Select all

revExecuteSQL myDBID, "UPDATE t_test Set a_Num = :1 WHERE id = :2", "myDataArray"
From the way SQL databases work we know that LC has to send some strings to the db to get any results. What strings are sent in this case?

Maybe something like this (mySQL version)?

Code: Select all

PREPARE Stm FROM 'UPDATE t_test Set a_Num = ? WHERE id = ?';
SET @a = 98;
SET @b = 3;
EXECUTE Stm USING @a, @b;
DEALLOCATE PREPARE Stm;
Above code isn't rocket science, it's just slightly advanced SQL useful for a speed-up in certain tasks.
But imagine you'd want to use such for a line-by-line UPDATE (where you PREPARE Stm once, repeat the SET, SET, EXECUTE part, and finish with DEALLOCATE) - using revExecuteSQL parametrized (in such way) for it would be quite a shot in the foot ...

So it would be nice to know what really happens here, without having to fire up the network sniffer & hacking it myself ;-)

Appendum:
Couldn't resist, fired up the network sniffer & tried it myself. After using the query above I changed it a bit ...
See the results:

Code: Select all

The code line in LC:
put revdb_execute(MyDBID,StrSQL,"theDatArr") into MyData

The variables in the debugger:
StrSQL: UPDATE t_test Set text = :1 WHERE id = :2;
theDatArr[1]: 55;SELECT * FROM t_test
theDatArr[2]: 3

Network sniffed:
44 00 00 00 03 55 50 44  41 54 45 20 74 5F 74 65   D....UPD ATE t_te 
73 74 20 53 65 74 20 74  65 78 74 20 3D 20 22 35   st Set t ext = "5 
35 3B 53 45 4C 45 43 54  20 2A 20 46 52 4F 4D 20   5;SELECT  * FROM  
74 5F 74 65 73 74 22 20  57 48 45 52 45 20 69 64   t_test"  WHERE id 
20 3D 20 22 33 22 3B 00                             = "3";. 

30 00 00 01 00 00 00 02  00 00 00 28 52 6F 77 73   0....... ...(Rows 
20 6D 61 74 63 68 65 64  3A 20 31 20 20 43 68 61    matched : 1  Cha 
6E 67 65 64 3A 20 30 20  20 57 61 72 6E 69 6E 67   nged: 0   Warning 
73 3A 20 30                                        s: 0
This is no injection, for sure. But I fail to see anything done by LC but a simple translation. Especially I don't see any measures against SQL injection.

Hmmm.
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

Post Reply

Return to “Databases”