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?

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:

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.

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:

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.

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.

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.

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.

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: