Sanitization
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Sanitization
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?
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Sanitization
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:
The actual workhorse function is rather fast & easy to use:
GPL v3, works with LC 6.7.10. Have fun!
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:
- 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 ; -> . - 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 actual workhorse function is rather fast & easy to use:
Code: Select all
UPDATE tTable SET fName='" & sanText(myInput) & "' WHERE ...
- Attachments
-
- awa_SimpleSan.zip
- a SimpleSan tool
- (4.93 KiB) Downloaded 250 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!
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!
Re: Sanitization
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.
Re: Sanitization
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
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Sanitization
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Sanitization
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:
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.
Parameterization means you are substituting SQL parameters (placeholders) for the values:
Code: Select all
SELECT * FROM bugs WHERE id=:1
Re: Sanitization
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Sanitization
Hi,
Have fun!
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:
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.If you use query parameters, sql-injection doesn't happen.
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!
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!
Re: Sanitization
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.
Re: Sanitization
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Sanitization
Hi,
I may have been too vague in my question. What I wanted to know was:
What does LC make with this statement:
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)?
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:
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.
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"
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;
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
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!
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!