> function createSanitisedString(s: string): SanitisedString
> function createSafeSqlQuery(s: SanitisedString): SqlQuery
The problem with the whole argument is that these functions are not actually enough to work with SQL, since they don't allow us to create dynamic SQL from safe strings.
Here are some ideas of why we can' use the functions you proposed:
user_filter = raw_user_input_col_name ++ " LIKE ?"
createSafeSQLQuery("SELECT * FROM my_table WHERE " ++ user_filter ++ ";") //type error
sanitizedQuery = createSanitisedString("SELECT * FROM my_table WHERE" ++ user_filter ++ ";") //should return error or quote everything
createSafeSQLQuery("SELECT * FROM my_table WHERE" ++ createSanitisedString(user_filter) ++ ";") //filter will be wrong, type error
Let's now add something to allow us to achieve our goal:
function asSanitizedString(s: string) : SanitizedString {
return s
}
user_filter = createSanitizedString(raw_user_input_col_name ) ++ asSanitizedString(" LIKE ?")
query = asSanitizedString("SELECT * FROM my_table WHERE") ++ user_filter ++ asSanitizedString(";")
createSafeSqlQuery(query) //works, nice
createSafeSqlQuery(asSanitizedString("SELECT * FROM my_table WHERE " ++ user_col_name ++ "LIKE ?;")) //oops, this also works
You can achieve all of this with a non-string API: you can have an SQL DSL or just an SQL AST library; or you can use an ORM. But either way, you can't fix it without modeling the entirety of SQL into your type system (or as much of SQL as you are willing to support).
If you don't believe me, go looking for a library that allows arbitrary strings as SQL, but statically ensures user input can't be used to construct an SQL query. I don't know of any one.
> The problem with the whole argument is that these functions are not actually enough to work with SQL
My bad for being unclear but I meant you would be sanitising something like the user entering "tea cups" into a shop search input form and searching your shop product database with that. I didn't mean the user would be entering SQL queries.
> But either way, you can't fix it without modeling the entirety of SQL into your type system
Using the type system to check correctness sounds good to me.
This is getting way too into the weeds about SQL anyway. Null dereference checks are a less distracting example to focus on for instance. As long as you can encode it into the type system, all my points are still relevant.
> My bad for being unclear but I meant you would be sanitising something like the user entering "tea cups" into a shop search input form and searching your shop product database with that. I didn't mean the user would be entering SQL queries.
No, I understood that. But my point is that the programmer is going to be composing SQL queries, and they will be doing it based on user input. The SQL API will have a very hard time distinguishing which parts it receives are trusted programmer input and which parts are untrusted user input.
> Using the type system to check correctness sounds good to me.
Sure, but SQL is a huge language with significant differences between any 2 major DB implementations (and that depends on SQL server configurations - e.g. in MySQL you can specify via config file whether "a" is a string or an identifier). I have never seen a full SQL DSL or AST used in any SQL client library, in any language: it's just too much work.
The problem with the whole argument is that these functions are not actually enough to work with SQL, since they don't allow us to create dynamic SQL from safe strings.
Here are some ideas of why we can' use the functions you proposed:
Let's now add something to allow us to achieve our goal: You can achieve all of this with a non-string API: you can have an SQL DSL or just an SQL AST library; or you can use an ORM. But either way, you can't fix it without modeling the entirety of SQL into your type system (or as much of SQL as you are willing to support).If you don't believe me, go looking for a library that allows arbitrary strings as SQL, but statically ensures user input can't be used to construct an SQL query. I don't know of any one.