Re: [sqlite] Securing user supplied SQL statements in a single process
Now, just for giggles here's the regex. It's for a simple SQL like Where
clause query language. Hopefully it makes sense :) According to my work
colleagues I am considered weird for enjoying writing a regex and they're
certainly more fun than waiting for the election result ;) (This was for a
personal project)
Cheers,
Mike
// setup the regex for tokenising the query.
//
// i. \G means match at the start of where the previous match finished
//
// ii. We used named groups (?<> with names that match the TokenTypes
enum so we can tell which command has been matched
// (except for Whitespace which when we find it we move onto the next
token)
//
// iii. The token this returns removes any separators e.g. [] for fields,
"" for string, ## for datetime. (also in a string "" indicates a double
quote)
//
// iv. The keywords below are in descending size order so that tokens such
as NotIn don't get confused with Not or In etc. (EndOfString is the shortest
// as it is the end of string so it's empty).
//
// v. As well as supporting the operators <, >, >=, <=, =, <> we also
support the alternatives GT, LT, GE, LE, EQ, NE, == and !=
string pattern = $@"\G(
(?$)|
(?\s+)|
(\#(?[^\#]*)\#)|
(\[(?[^\]]*)\])|
(?({Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.PositiveSign)}|{Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.NegativeSign)})?\d*{Regex.Escape(CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator)}?\d+)|
(""(?([^""]|)*)"")|
(?Not\s*Like)|
(?Between)|
(?Not\s*In)|
(?Empty)|
(?{Regex.Escape(bool.FalseString)})|
(?Like)|
(?{Regex.Escape(bool.TrueString)})|
(?And)|
(?Not)|
(?Yes)|
(?EQ)|
(?==)|
(?GT)|
(?\>=)|
(?GE)|
(?In)|
(?LT)|
(?\<=)|
(?LE)|
(?\<\>)|
(?NE)|
(?!=)|
(?No)|
(?Or)|
(?Y)|
(?N)|
(?{Regex.Escape(bool.TrueString.Substring(0,
1))})|
(?{Regex.Escape(bool.FalseString.Substring(0,
1))})|
(?\))|
(?,)|
(?=)|
(?\>)|
(?\<)|
(?\())";
On Thu, 12 Dec 2019 at 20:06, Mike King wrote:
> It was a very simple cutdown SQL (think just a where clause) and the regex
> was a multiline affair which picked out tokens using named groups. I then
> had a function that skipped whitespace tokens and returned the next token
> (group name) and the value (parsed and validated for dates and numeric
> values, strings were escaped).
>
> Secondly, my code is designed to run on many databases not just SQLite and
> I don’t have access at the level you describe from a .Net driver. So using
> something specific to one manufacturer is not a good idea nor is writing
> custom code for each database.
>
> All this was possible and I just added my experiences to show it is
> possible with a little thought.
>
> Cheers
>
>
>
> On Thu, 12 Dec 2019 at 19:29, Warren Young wrote:
>
>> On Dec 12, 2019, at 6:08 AM, Mike King wrote:
>> >
>> > ...I decided on a simple subset of
>> > SQL and then wrote a parser using a regex as the tokeniser.
>>
>> First, [SQL is not a regular language][1], so it probably cannot be
>> completely parsed by regexes. Not by a single regex without surrounding
>> logic, anyway. There’s probably valid SQL that will pass your regex but
>> give unwanted behavior.
>>
>> Second, you’re reinventing SQLite’s own authorizer, which runs based on
>> the output of SQLite’s own well-tested SQL parsing engine. (Which uses a
>> proper parser, not a regex.)
>>
>> Security is not an area where you want to roll your own logic. Use
>> someone else’s well-tested solution whenever you can.
>>
>>
>> [1]: https://stackoverflow.com/a/33415289/142454
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
It was a very simple cutdown SQL (think just a where clause) and the regex was a multiline affair which picked out tokens using named groups. I then had a function that skipped whitespace tokens and returned the next token (group name) and the value (parsed and validated for dates and numeric values, strings were escaped). Secondly, my code is designed to run on many databases not just SQLite and I don’t have access at the level you describe from a .Net driver. So using something specific to one manufacturer is not a good idea nor is writing custom code for each database. All this was possible and I just added my experiences to show it is possible with a little thought. Cheers On Thu, 12 Dec 2019 at 19:29, Warren Young wrote: > On Dec 12, 2019, at 6:08 AM, Mike King wrote: > > > > ...I decided on a simple subset of > > SQL and then wrote a parser using a regex as the tokeniser. > > First, [SQL is not a regular language][1], so it probably cannot be > completely parsed by regexes. Not by a single regex without surrounding > logic, anyway. There’s probably valid SQL that will pass your regex but > give unwanted behavior. > > Second, you’re reinventing SQLite’s own authorizer, which runs based on > the output of SQLite’s own well-tested SQL parsing engine. (Which uses a > proper parser, not a regex.) > > Security is not an area where you want to roll your own logic. Use > someone else’s well-tested solution whenever you can. > > > [1]: https://stackoverflow.com/a/33415289/142454 > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
On 12/12/19, Warren Young wrote: > On Dec 12, 2019, at 6:08 AM, Mike King wrote: >> >> ...I decided on a simple subset of >> SQL and then wrote a parser using a regex as the tokeniser. > > First, [SQL is not a regular language][1], so it probably cannot be > completely parsed by regexes. Not by a single regex without surrounding > logic, anyway. There’s probably valid SQL that will pass your regex but > give unwanted behavior. > > Second, you’re reinventing SQLite’s own authorizer, which runs based on the > output of SQLite’s own well-tested SQL parsing engine. (Which uses a proper > parser, not a regex.) Excellent points. Nevertheless, I think you can make the case for using a home-grown regexp-based language restrictor *in addition to* the built-in SQLite authorizer. Layers of defense. So my advice to the Mr. King is not to take out his regexp-based language restrictor but rather to add in an additional layer based on the SQLite authorizer, for redundancy. During development and testing, there should be some means of turning each mechanism off separately so that the other can be tested, but in deployment both should be hard-wired on. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
On Dec 12, 2019, at 6:08 AM, Mike King wrote: > > ...I decided on a simple subset of > SQL and then wrote a parser using a regex as the tokeniser. First, [SQL is not a regular language][1], so it probably cannot be completely parsed by regexes. Not by a single regex without surrounding logic, anyway. There’s probably valid SQL that will pass your regex but give unwanted behavior. Second, you’re reinventing SQLite’s own authorizer, which runs based on the output of SQLite’s own well-tested SQL parsing engine. (Which uses a proper parser, not a regex.) Security is not an area where you want to roll your own logic. Use someone else’s well-tested solution whenever you can. [1]: https://stackoverflow.com/a/33415289/142454 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
I had to do something similar. I’m the end I decided on a simple subset of SQL and then wrote a parser using a regex as the tokeniser. The output was SQL. By doing it this way I could validate field names and make sure all values were correctly formatted and escaped. Cheers On Thu, 12 Dec 2019 at 13:01, Richard Hipp wrote: > On 12/12/19, test user wrote: > > Hello, > > > > How can I secure user supplied SQL statements in a single process? > > See https://www.sqlite.org/security.html for an introduction. > > Other suggestions: > > (1) Run the process that is evaluating user-supplied SQL in a sandbox, > where it can do no harm if it does malfunction. > > (2) Limit the SQL to verified users. Do not allow SQL from anonymous > robots on the internet. > > (3) Restrict the scope of SQL using the authorizer, if possible. > > (4) Always use the latest available version of SQLite with all the > latest patches. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
On 12/12/19, test user wrote: > Hello, > > How can I secure user supplied SQL statements in a single process? See https://www.sqlite.org/security.html for an introduction. Other suggestions: (1) Run the process that is evaluating user-supplied SQL in a sandbox, where it can do no harm if it does malfunction. (2) Limit the SQL to verified users. Do not allow SQL from anonymous robots on the internet. (3) Restrict the scope of SQL using the authorizer, if possible. (4) Always use the latest available version of SQLite with all the latest patches. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Securing user supplied SQL statements in a single process
On Thu, Dec 12, 2019 at 1:47 PM test user wrote: > How can I secure user supplied SQL statements in a single process? > The one mechanism SQLite has is the authorizer [1]. Whether that's good enough for you, that's for you to determine. --DD [1] https://www.sqlite.org/c3ref/set_authorizer.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Securing user supplied SQL statements in a single process
Hello, How can I secure user supplied SQL statements in a single process? For example, if I had a public web service that allows users to create their own SQL strings that I then run in a single server process, what are the chances that they would be able to obtain general remote code execution? I saw a security flaw a while back, and most people mentioned that “you should not run user supplied SQL statements”. Is there a way to do this using only SQLite (without spawning many processes or using OS level isolation)? Thanks ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
