Copyright © Yariv Sadan 2006-2007
Authors: Yariv Sadan (yarivvv@gmail.com) [web site: http://yarivsblog.com].
ErlSQL is a domain specific embedded language for expressing SQL statements in Erlang as well as a library for generating the literal equivalents of ErlSQL expressions.
ErlSQL lets you describe SQL queries using a combination of Erlang lists, tuples, atoms and values in a way that resembles the structure of SQL statements. You can pass this structure to the sql/1 or sql/2 functions, which parse it and return an iolist (a tree of strings and/or binaries) or a single binary, either of which can be sent to database engine through a socket (usually via a database-specific driver).
ErlSQL supports a large subset of the SQL language implemented by some popular RDBMS's, including most common INSERT, UPDATE, DELETE and SELECT statements. ErlSQL can generate complex queries including those with unions, nested statements and aggregate functions, but it does not currently attempt to cover every feature and extension of the SQL language.
ErlSQL's benefits are:
- Easy dynamic generation of SQL queries from Erlang by combining
native Erlang types rather than string fragments.
- Prevention of most, if not all, SQL injection attacks by
assuring that all string values are properly escaped.
- Efficient generation of iolists as nested lists of binaries.
Warning: ErlSQL allows you to write verbatim WHERE clauses as well as verbatim LIMIT and other trailing clauses, but using this feature is highly discouraged because it exposes you to SQL injection attacks.
For usage examples, look at the file test_erlsql.erl under the test/ directory.| encode/1 | Calls encode(Val, true). |
| sql/1 | Generate an iolist (a tree of strings and/or binaries) for a literal SQL statement that corresponds to the ESQL structure. |
| sql/2 | Similar to sql/1, but accepts a boolean parameter indicating if the return value should be a single binary rather than an iolist. |
| unsafe_sql/1 | Generate an iolist (a tree of strings and/or binaries) for a literal SQL statement that corresponds to the ESQL structure. |
| unsafe_sql/2 | Similar to unsafe_sql/1, but accepts a boolean parameter indicating if the return value should be a binary or an iolist. |
encode(Val::term()) -> binary()
Calls encode(Val, true).
sql(ErlSQL::term()) -> iolist()
Generate an iolist (a tree of strings and/or binaries) for a literal SQL statement that corresponds to the ESQL structure. If the structure is invalid, this function would crash. This function does not allow writing literal WHERE, LIMIT and other trailing clauses. To write such clauses, call unsafe_sql/1 or unsafe_sql/2.
sql(ErlSQL::term(), X2::boolean()) -> binary() | iolist()
Similar to sql/1, but accepts a boolean parameter indicating if the return value should be a single binary rather than an iolist.
unsafe_sql(ErlSQL::term()) -> iolist()
throws {error, {unsafe_expression, Expr}}
Generate an iolist (a tree of strings and/or binaries) for a literal SQL statement that corresponds to the ESQL structure. If the structure is invalid, this function throws an exception. This function allows writing literal WHERE, LIMIT and other trailing clauses, such as {where, "a=" ++ Val}, or "WHERE a=" ++ Str ++ " LIMIT 5". Such clauses are unsafe because they expose you to SQL injection attacks. When you use unsafe_sql, make sure to quote all your strings using the encode/1 function.
unsafe_sql(ErlSQL::term(), AsBinary::bool()) -> binary() | iolist()
throws {error, {unsafe_expression, Expr}}
Similar to unsafe_sql/1, but accepts a boolean parameter indicating if the return value should be a binary or an iolist.