Fork me on GitHub
2sxc 9.0 for DNN 7 to 9
Website Builder, Content Manager, App-System: free and amazing - done your way
You are here: Home  >  Docs  >  Feature

DnnSqlDataSources

Introduced in Version 07.00.00

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

  1. All token parameters are used as SQL-Parameters. This makes SQL-Injection impossible
  2. 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. 

Tags for this feature

2serve . 2invent . 2create is 2be.