Kentico CMS has a ton of great security built-in that helps ensure your site is safe from malicious attacks. Sometimes that security can prohibit functionality that you may need in your site. This blog post is to explain a way around the SQL Injection in a web part, assuming you want to do so.
Background
I recently worked on a project that I needed to dynamically build up a Where Condition for a Repeater web part, based on Query String values. In my web part properties, I configured the Where Condition to first check if the QS value was not NULL and then if it was a number. Once I verified this value was valid, I attempted to build up the Where Condition, using the QS values. (“[ColumnName] LIKE ‘%” + QS.Value + “%’”).
This is the K# code I used:
{%if (QueryString.GetValue("id") != null){if (ToInt(QueryString.GetValue("id")) > 0){"NewsID LIKE '%" + QueryString.GetValue("id") + "%'"}} |(identity)GlobalAdministrator|(hash)34853d2e059c167c368f2e6beaf9a2e27e428d9bb3c863caca1333b7a313baca%}
Here is the web part property setting I configured:
Web part properties
K# code
NOTE
ALWAYS check Query String values when using them within code. Passing SQL commands over query string is one of the most common forms of SQL injection so be sure to filter out any of these commands in your code.
After setting those values, I attempted to access my page.
Error page
Never a good message to get...
After trying various changes in the K# code and getting errors about my syntax, I enabled debugging on the site. After hitting the page again and checking the executed query, I quickly saw the issue. The Where Condition was getting duplicate apostrophes in my LIKE condition.
Debug log with error
Here is the SQL that got executed:
SELECT NewsTeaser, NewsTitle, NewsSummary, NewsText, NewsReleaseDate, NodeAliasPath, DocumentURLPath, SiteName, NodeOwner, NodeAlias, DocumentCulture, NodeID, NodeLinkedNodeID, ClassName FROM View_CONTENT_News_Joined WHERE (((((NodeSiteID = 1) AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/News/%')) AND (NewsID LIKE ''%1%'')) ORDER BY NewsReleaseDate DESC
The reason for this is that web parts, by default, have built in SQL Injection protection. This protection replaces any apostrophes with double-apostrophes to prevent unwanted code execution. While most of the time this is a good thing, in this case it was preventing what I trying to do. After trying standard escape characters and a lot of debugging, I found the cause of the issue in the Developer’s Guide
here.
The Solution
I followed the Developer’s Guide, however, elected to add a property to the web part that could be set by the user. This allows the setting to be set on a web part level, as opposed to on a site level and affecting every usage.
Web Part Code
/// <summary>
/// Gets or sets the value that indicates if the properties should have SQL Injection protection disabled.
/// </summary>
public bool DisableSQLInjectionProtection
{
get
{
return ValidationHelper .GetBoolean(GetValue(" DisableSQLInjectionProtection" ), false );
}
set
{
SetValue(" DisableSQLInjectionProtection" , value );
}
}
/// <summary>
/// Initializes the control properties.
/// </summary>
protected void SetupControl()
{
if (StopProcessing)
{
repItems.StopProcessing = true ;
}
else
{
//Check if SQL Injection protection is disabled
if (this .DisableSQLInjectionProtection)
{
this .SQLProperties = "" ;
}
NOTE
Typically I would not recommend modifying Kentico’s existing web part. This is just for demonstration purposes and brevity.
I added the property to the existing web part under a new section (Custom).
New web part property
I then updated the web part properties, using my new setting.
Set web part property
After setting the new property, I test the page again and got the desired results:
Working page
I checked the debug log again to make sure the SQL was correct.
Debug log with working SQL
Here is the SQL that got executed after updating the web part:
SELECT NewsTeaser, NewsTitle, NewsSummary, NewsText, NewsReleaseDate, NodeAliasPath, DocumentURLPath, SiteName, NodeOwner, NodeAlias, NodeXML, DocumentCulture, NodeID, NodeLinkedNodeID, ClassName FROM View_CMS_Tree_Joined_Versions INNER JOIN CONTENT_News ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = CONTENT_News.[NewsID] WHERE ((((NodeSiteID = 1) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/News/%')) AND (NewsID LIKE '%1%')) AND (ClassName = 'CMS.News') ORDER BY NewsReleaseDate DESC
Final Thoughts
Ultimately, this is a workaround for an issue within K# itself. Ideally, I feel an escape character(s) in K# to allow apostrophes would be best. This would prevent having to make a new web part (or modify an existing one) and setting the fields to disable protection manually. I did submit a User Voice entry for this so hopefully we see in a future release.
Note
Kentico’s User Voice feature allows clients to submit ideas to the product. Kentico values these entries and implements them in the product, when possible.