A DataSource allowing you to add SQL but only run on the DNN Database. It also prevents you from using statements such as Insert and protects you against SQL Injection.
This is what it looks like in the Visual Query Designer
Streams in the DnnSqlDataSource
- In - non expected
- It expects no In-Streams. If you add any, you can use them in the queries (for example to provide configuration)
- Out - Default only
- Default - containing the result of the SQL-Query
Configuration of the DnnSqlDataSource
- ContentType - this is the internal type-name of the results. Just give it something you'll understand - there are only few cases where this is important
- SelectCommand - the SQL used to retrieve the data
Using Tokens in the SQL-Query
You'll need this a lot, for example to formulate Select * from Products Where ProductId = [QueryString:id||0]
If you want to use another DataSource in the In-Stream to use that in configuring your query, see the related feature In-ValueProvider. It will allow you to create tokens like [In:ModuleContent:ProductCategory] etc.
Specials of the DnnSqlDataSource
You need to specify a column for EntityId and Entity Title. So your queries will usually be something like
Select Id as EntityId, ProductName as EntityTitle, * From Products
Protection Against SQL Injection, Insert-Statements and More
One of the biggest risks in using SQL is SQL-Injection - because an attacker could try to place SQL-Code into the parameters to try to rewrite the SQL into a form which he could abuse. The DnnSqlDataSource is based on the SqlDataSource and automatically protects you in 2 important ways
- All token parameters are used as SQL-Parameters. This makes SQL-Injection impossible
- The query is controlled for all known Insert/Modification statements. So only Select-Statements are allowed. In rare cases this may stop a normal query if you have columns with suspicious names.
With these two security measures you should be very safe.