Page 1 of 1
Sanitization
Posted: Mon Oct 30, 2017 10:58 am
by MaxV
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?
Re: Sanitization
Posted: Mon Oct 30, 2017 12:02 pm
by AxWald
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:
- 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 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!
Re: Sanitization
Posted: Mon Oct 30, 2017 2:46 pm
by Mikey
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
Posted: Mon Oct 30, 2017 6:10 pm
by MaxV
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.
Re: Sanitization
Posted: Mon Oct 30, 2017 6:28 pm
by MaxV
is base64encode enough?
Re: Sanitization
Posted: Mon Oct 30, 2017 6:41 pm
by Mikey
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.
Re: Sanitization
Posted: Tue Oct 31, 2017 11:40 am
by MaxV
Wow, I made some tests and you are right!!!! Great!!!
Re: Sanitization
Posted: Wed Nov 01, 2017 5:12 pm
by AxWald
Hi,
Mikey wrote: ↑Mon Oct 30, 2017 6:41 pm
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!
Re: Sanitization
Posted: Wed Nov 01, 2017 5:32 pm
by Mikey
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
Posted: Thu Nov 02, 2017 2:49 pm
by MaxV
AxWald wrote: ↑Wed Nov 01, 2017 5:12 pm
Hi,
Mikey wrote: ↑Mon Oct 30, 2017 6:41 pm
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.
Re: Sanitization
Posted: Mon Nov 06, 2017 1:28 pm
by AxWald
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.