SQL Command
The SQL Lava command allows you to run SQL, returning the results in a Lava variable that you can then
iterate over. Let's see it in action.
{% sql %}
SELECT [NickName], [LastName] FROM [Person]
WHERE [LastName] = 'Decker'
{% endsql %}
{% for item in results %}
{{ item.NickName }} {{ item.LastName }} <br />
{% endfor %}
In this example we're querying for all of the people in the database
with the last name of 'Decker' and then writing out the results.
Your SQL can also contain Lava. When adding Lava variables, you need to be VERY careful to not allow
SQL injection attacks.
{% assign lastName = 'Decker' %}
{% sql %}
SELECT [NickName], [LastName] FROM [Person]
WHERE [LastName] = '{{ lastName }}'
{% endsql %}
{% for item in results %}
{{ item.NickName }} {{ item.LastName }} <br />
{% endfor %}
SQL Commands
What about UPDATE and DELETE? You can actually use the syntax above to run updates and deletes, but if you
want to get the number of rows returned you'll need to add the 'statement' parameter like:
{% sql statement:'command' %}
DELETE FROM [DefinedValue] WHERE [Id] IN (186,187)
{% endsql %}
{{ results }} {{ 'record' | PluralizeForQuantity:results }} were deleted.
If you update something directly via SQL, the cache manager won't know about it so you'll need to
take care of flushing it from cache yourself.
Again be VERY careful about SQL injection. DROP statements do work in the Lava SQL command.
Note: Even if you're not selecting anything (such as in an INSERT or UPDATE statement),
you'll still need to set a 'results' Attribute for the SQL to execute.
Changing The Return Variable
By default the return variable is always called 'results'. You can change this however by providing the 'return' parameter.
{% sql return:'mylist' %}
SELECT [NickName], [LastName] FROM [Person]
WHERE [LastName] = 'Decker'
{% endsql %}
{% for item in mylist %}
{{ item.NickName }} {{ item.LastName }} <br />
{% endfor %}
SQL Parameters v8.0
Earlier we mentioned that you should be very careful about SQL injection when using this command.
One way to prevent this is to use parameters. This ensures that whatever value you are going to pass
through to the SQL statement will not be executed as SQL.
Parameters can be passed by way of the {% sql %}
line in the block.
Any parameter:'value'
pairs will be treated as parameters and made available to the SQL
statement. The two exceptions are 'statement' and 'return', which may not be used for passing parameters
as they already have their respective purposes as described above. SQL parameters are referenced in the SQL statement
by prefixing the property name with '@'.
Let's revisit a previous example but use SQL parameters instead of placing the variable inline with the
SQL statement.
{% assign lastName = 'Decker' %}
{% sql name:'{{ lastName }}' %}
SELECT [NickName], [LastName] FROM [Person]
WHERE [LastName] = @name
{% endsql %}
{% for item in results %}
{{ item.NickName }} {{ item.LastName }} <br />
{% endfor %}