private DataTable GetDataSource(Report report, DataSource dataSource)
{
var parameters = new ParameterInfo
{
Parameters = report.Parameters,
DataSourceParameters = dataSource.Parameters
};
return DataSourceHelper.GetDataTable(report.Tenant, dataSource.Query, parameters);
}
public static DataTable GetDataTable(string tenant, string sql, ParameterInfo parameters)
{
/**************************************************************************************
A Frapid report is a developer-only feature.
But, that does not guarantee that there will be no misuse.
So, the possible risk factor cannot be ignored altogether in this context.
Therefore, a review for defense against possible
SQL Injection Attacks is absolutely required here.
Please do note that you should connect to Database Server using a login "report_user"
which has a read-only access for executing the SQL statements to produce the report.
The SQL query is expected to have only the SELECT statement, but there is no
absolute and perfect way to parse and determine that the query contained
in the report is actually a "SELECT-only" statement.
Moreover, the prospective damage could occur due to somebody messing up
with the permission of the database user "report_user" which is restricted by default
with a read-only access.
This could happen on the DB server, where we cannot "believe"
that the permissions are perfectly intact.
TODO: Investigate more on how this could be done better.
***************************************************************************************/
if (string.IsNullOrWhiteSpace(sql))
{
return null;
}
//A separate connection to database using a restricted login is established here.
string connectionString = FrapidDbServer.GetReportUserConnectionString(tenant, tenant);
var site = TenantConvention.GetSite(tenant);
string providerName = site.DbProvider;
if (providerName == "Npgsql")
{
return GetPostgresDataTable(connectionString, sql, parameters);
}
return GetSqlServerDataTable(connectionString, sql, parameters);
}
private static object GetParameterValue(string name, string type, ParameterInfo info)
{
var paramter = info.Parameters.FirstOrDefault(x => x.Name.ToLower().Equals(name.Replace("@", "").ToLower()));
if (paramter != null)
{
return DataSourceParameterHelper.CastValue(paramter.Value, type);
}
foreach (var dataSourceParameter in info.DataSourceParameters)
{
if (dataSourceParameter.Name.ToLower().Equals(name.ToLower()))
{
if (dataSourceParameter.DefaultValue != null)
{
return dataSourceParameter.DefaultValue;
}
}
}
return null;
}
private static DataTable GetPostgresDataTable(string connectionString, string sql, ParameterInfo info)
{
using (var connection = new NpgsqlConnection(connectionString))
{
using (var command = new NpgsqlCommand(sql, connection))
{
if (info.DataSourceParameters != null)
{
foreach (var p in info.DataSourceParameters)
{
command.Parameters.AddWithNullableValue(p.Name, GetParameterValue(p.Name, p.Type, info));
}
}
connection.Open();
using (var table = new DataTable())
{
table.Load(command.ExecuteReader());
return table;
}
}
}
}
public static string ParseExpression(string tenant, string expression, List<DataSource> dataSources, ParameterInfo info)
{
if (string.IsNullOrWhiteSpace(expression))
{
return string.Empty;
}
string logo = GetLogo();
if (!string.IsNullOrWhiteSpace(logo))
{
//Or else logo will not be exported into excel.
expression = expression.Replace("{LogoPath}", GetCurrentDomainName() + VirtualPathUtility.ToAbsolute(logo));
}
expression = expression.Replace("{PrintDate}", DateTime.Now.ToString(CultureManager.GetCurrentUiCulture()));
foreach (var match in Regex.Matches(expression, "{.*?}"))
{
string word = match.ToString();
if (word.StartsWith("{Meta.", StringComparison.OrdinalIgnoreCase))
{
string sessionKey = RemoveBraces(word);
sessionKey = sessionKey.Replace("Meta.", "");
sessionKey = sessionKey.Trim();
string value = GetDictionaryValue(tenant, sessionKey);
expression = expression.Replace(word, value);
}
else if (word.StartsWith("{Query.", StringComparison.OrdinalIgnoreCase))
{
string res = RemoveBraces(word);
var resource = res.Split('.');
string key = resource[1];
var parameter = info.Parameters.FirstOrDefault(x => x.Name.ToLower().Equals(key.ToLower()));
if (parameter != null)
{
string value = FormattingHelper.GetFormattedValue(parameter.Value);
var datasourceParameter = info.DataSourceParameters.FirstOrDefault(x => x.Name.Replace("@", "").ToLower().Equals(parameter.Name.ToLower()));
if (datasourceParameter != null)
{
string type = datasourceParameter.Type;
value = DataSourceParameterHelper.CastValue(value, type).ToString();
}
expression = expression.Replace(word, value);
}
}
else if (word.StartsWith("{Resources.", StringComparison.OrdinalIgnoreCase))
{
string res = RemoveBraces(word);
var resource = res.Split('.');
string key = resource[2];
expression = expression.Replace(word, ResourceManager.GetString(tenant, resource[1], key));
}
else if (word.StartsWith("{DataSource", StringComparison.OrdinalIgnoreCase) &&
word.ToLower(CultureInfo.InvariantCulture).Contains("runningtotalfieldvalue"))
{
string res = RemoveBraces(word);
var resource = res.Split('.');
int dataSourceIndex =
resource[0].ToLower(CultureInfo.InvariantCulture)
.Replace("datasource", "")
.Replace("[", "")
.Replace("]", "").To<int>();
int index =
resource[1].ToLower(CultureInfo.InvariantCulture)
.Replace("runningtotalfieldvalue", "")
.Replace("[", "")
.Replace("]", "").To<int>();
if (dataSourceIndex >= 0 && index >= 0)
{
var dataSource = dataSources.FirstOrDefault(x => x.Index.Equals(dataSourceIndex));
if (dataSource?.Data != null)
{
expression = expression.Replace(word,
GetSum(dataSource.Data, index)
.ToString(CultureInfo.InvariantCulture));
}
}
}
//else if (word.StartsWith("{Barcode", StringComparison.OrdinalIgnoreCase))
//{
//string res = RemoveBraces(word).Replace("Barcode(", "").Replace(")", "");
//string barCodeValue = res;
// if (res.StartsWith("DataSource"))
// {
// barCodeValue = ParseDataSource("{" + res + "}", dataTableCollection);
// }
// string barCodeFormat = ConfigurationHelper.GetReportParameter("BarCodeFormat");
// string barCodeDisplayValue = ConfigurationHelper.GetReportParameter("BarCodeDisplayValue");
// string barCodeFontSize = ConfigurationHelper.GetReportParameter("BarCodeFontSize");
// string barCodeWidth = ConfigurationHelper.GetReportParameter("BarCodeWidth");
// string barCodeHeight = ConfigurationHelper.GetReportParameter("BarCodeHeight");
// string barCodeQuite = ConfigurationHelper.GetReportParameter("BarCodeQuite");
// string barCodeFont = ConfigurationHelper.GetReportParameter("BarCodeFont");
// string barCodeTextAlign = ConfigurationHelper.GetReportParameter("BarCodeTextAlign");
// string barCodeBackgroundColor = ConfigurationHelper.GetReportParameter("BarCodeBackgroundColor");
// string barCodeLineColor = ConfigurationHelper.GetReportParameter("BarCodeLineColor");
// string imageSource =
// "<img class='reportEngineBarCode' data-barcodevalue='{0}' alt='{0}' value='{0}' data-barcodeformat='{1}' data-barcodedisplayvalue='{2}' data-barcodefontsize='{3}' data-barcodewidth='{4}' data-barcodeheight='{5}' data-barcodefont='{6}' data-barcodetextalign='{7}' data-barcodebackgroundcolor='{8}' data-barcodelinecolor='{9}' data-barcodequite={10} />";
// imageSource = string.Format(CultureInfo.InvariantCulture, imageSource, barCodeValue,
// barCodeFormat, barCodeDisplayValue, barCodeFontSize, barCodeWidth, barCodeHeight,
// barCodeFont, barCodeTextAlign, barCodeBackgroundColor, barCodeLineColor, barCodeQuite);
// expression = expression.Replace(word, imageSource).ToString(CultureInfo.InvariantCulture);
//}
//else if (word.StartsWith("{QRCode", StringComparison.OrdinalIgnoreCase))
//{
// string res = RemoveBraces(word).Replace("QRCode(", "").Replace(")", "");
// string qrCodeValue = res;
// if (res.StartsWith("DataSource"))
// {
// qrCodeValue = ParseDataSource("{" + res + "}", dataTableCollection);
// }
// string qrCodeRender = ConfigurationHelper.GetReportParameter("QRCodeRender");
// string qrCodeBackgroundColor = ConfigurationHelper.GetReportParameter("QRCodeBackgroundColor");
// string qrCodeForegroundColor = ConfigurationHelper.GetReportParameter("QRCodeForegroundColor");
// string qrCodeWidth = ConfigurationHelper.GetReportParameter("QRCodeWidth");
// string qrCodeHeight = ConfigurationHelper.GetReportParameter("QRCodeHeight");
// string qrCodeTypeNumber = ConfigurationHelper.GetReportParameter("QRCodeTypeNumber");
// string qrCodeDiv =
// "<div class='reportEngineQRCode' data-qrcodevalue={0} data-qrcoderender='{1}' data-qrcodebackgroundcolor='{2}' data-qrcodeforegroundcolor='{3}' data-qrcodewidth='{4}' data-qrcodeheight='{5}' data-qrcodetypenumber='{6}'></div>";
// qrCodeDiv = string.Format(CultureInfo.InvariantCulture, qrCodeDiv, qrCodeValue, qrCodeRender,
// qrCodeBackgroundColor, qrCodeForegroundColor, qrCodeWidth, qrCodeHeight, qrCodeTypeNumber);
// expression = expression.Replace(word, qrCodeDiv).ToString(CultureInfo.InvariantCulture);
//
//}
}
return expression;
}