Fork me on GitHub
2sxc 9.33 for DNN 7 to 9
Website Builder, Content Manager, App-System: open-source and amazing
You are here: Home  >  Docs  >  Feature

SqlDataSource

Introduced in Version 07.00.00

A DataSource allowing you to add SQL to run against any 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

This DataSource is currently regarded as "unsafe" because non-host users could use it to access other data bases. So for the moment the SqlDataSource is NOT available in the Visual Query Designer.  

But you can always use the DnnSqlDataSource for any data from DNN. 

Streams in the SqlDataSource

  • 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 SqlDataSource

Since you can't configure it in the visual designer, the following properties are only available in C# code. 

  • ConnectionString - the SQL Connection string
  • 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 SqlDataSource

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 SqlDataSource 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