My end goal is to store some parameters used to generate some SQL used for reporting in an attempt to reduce (or remove?) the possibility of SQL Injection.
So, in part of my solution I plan on creating a report definition in an admin panel of my application. The report definition is the raw SQL used to generate the output, and then there is a parameter definition to present to the user in a UI and try and keep things strongly typed.
The parameters is where I'm currently stuck. I can serialize the list of parameters properly and the XML looks like what I was expecting but attempting to deserialize results in some exceptions.
I tried to build a simple method to turn an XML string into an object and got some yuk. I understand the exception and the cause, I just don't know how to resolve.
When I try to deserialize an object that is generic it fails. In the Xml a generic might look like this:
<?xml version="1.0" encoding="utf-16"?>
<ReportParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Parameters>
<ReportParameter xsi:type="ReportParameterOfInt32">
<Name>Test</Name>
<Value xsi:type="xsd:int">4</Value>
</ReportParameter>
<ReportParameter xsi:type="ReportParameterOfDateTime">
<Name>Startdate</Name>
<Value xsi:type="xsd:dateTime">2019-04-05T22:52:25.9869948-05:00</Value>
</ReportParameter>
</Parameters>
</ReportParameters>
The type on ReportParameter I think is my hangup. xsi:type="ReportParameterOfInt32"
Should resolve to ReportParameter (or similar)
Here is what I have to serialize/deserialize what I'm talking about.
public class ReportParameter
{
/// <summary>
/// All names are prefixed automatically with the @ sign.
/// This is the name that will appear in the query when building/writing
/// </summary>
public string Name { get; set; }
public object Value { get; set; }
}
/// <summary>
/// A generic object type that can hold many data types
/// </summary>
/// <typeparam name="T"></typeparam>
public class ReportParameter<T> : ReportParameter
{
T _value;
[XmlIgnore]
public new T Value
{
get { return _value; }
set
{
base.Value = value;
_value = value;
}
}
}
Then I have an extension method to serialize the data (which would eventually be written to a table)
public static string Serialize(this ReportParameters parameters)
{
List<Type> types = new List<Type>();
// Loop over all the parameters
foreach (var a in parameters.Parameters)
{
// See if this Type is already in the list of potential types
if (types.Contains(a.GetType()))
{
continue;
}
// Add it to the List
types.Add(a.GetType());
}
types.Add(typeof(ReportParameters));
Type[] genericTypes = types.ToArray();
XmlSerializer xsSubmit = new XmlSerializer(typeof(ReportParameters), genericTypes);
string xml = string.Empty;
using (var sww = new StringWriter())
{
using (var writer = XmlWriter.Create(sww))
{
xsSubmit.Serialize(writer, parameters);
xml = sww.ToString();
return xml;
}
}
}
After writing a test method to do a simple deserialization I get a lovely exception:
Message: Test method X.Tests.TestSerialize.SerializeGeneric threw exception: System.InvalidOperationException: There is an error in XML document (1, 170). ---> System.InvalidOperationException: The specified type was not recognized: name='ReportParameterOfInt32', namespace='', at .
Any help here would be greatly appreciated.
I have a solution for you but not that matches your expectation, unfortunately. I don't know if you tried this but I hope that this will help you at least.
Correct me if I am wrong but it doesn't seem like there is support for what you are trying to accomplish, unless you roll out your own Deserializer.
What might be missing is a way to inform the Xml Serializer of using the Generic class ReportParameter<T> and which Types of T it should account for.
So instead of going with the Generic Parameter approach, I hand-rolled those classes explicitly.
public class ReportParameterOfInt32 : ReportParameter
{
int _value;
[XmlIgnore]
public new int Value
{
get { return _value; }
set
{
base.Value = value;
_value = value;
}
}
}
public class ReportParameterOfDateTime : ReportParameter
{
DateTime _value;
[XmlIgnore]
public new DateTime Value
{
get { return _value; }
set
{
base.Value = value;
_value = value;
}
}
}
Updated the ReportParameters (which I guessed how it looks like since it wasn't provided in your question):
public class ReportParameters
{
[XmlArrayItem(typeof(ReportParameterOfInt32))]
[XmlArrayItem(typeof(ReportParameterOfDateTime))]
public ReportParameter[] Parameters { get; set; }
}
Simplified the Serializer and wrote the Deserializer:
private static string Serialize(ReportParameters parameters)
{
XmlSerializer xsSubmit = new XmlSerializer(typeof(ReportParameters));
string xml = string.Empty;
using (var sww = new StringWriter())
{
using (var writer = XmlWriter.Create(sww))
{
xsSubmit.Serialize(writer, parameters);
xml = sww.ToString();
return xml;
}
}
}
private static ReportParameters Deserialize(string xml)
{
XmlSerializer xsSubmit = new XmlSerializer(typeof(ReportParameters));
using (var reader = new StringReader(xml))
{
return (ReportParameters)xsSubmit.Deserialize(reader);
}
}
Wrote the test code which then executed as expected:
var xml = Serialize(
new ReportParameters
{
Parameters = new ReportParameter[]
{
new ReportParameterOfInt32 { Name = "Test", Value = 4 },
new ReportParameterOfDateTime { Name = "Startdate", Value = new DateTime(2019, 04, 05, 22, 52, 25) }
}
});
var obj = Deserialize(xml);
Producing:
<?xml version="1.0" encoding="utf-16"?>
<ReportParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Parameters>
<ReportParameterOfInt32>
<Name>Test</Name>
<Value xsi:type="xsd:int">4</Value>
</ReportParameterOfInt32>
<ReportParameterOfDateTime>
<Name>Startdate</Name>
<Value xsi:type="xsd:dateTime">2019-04-05T22:52:25</Value>
</ReportParameterOfDateTime>
</Parameters>
</ReportParameters>
I know you might feel a little dissapointed, however, I do think that you can just use wrappers to ensure that it writes to the underlying type correctly without having to serialize the Generic type which should hopefully satisfy your "SQL Injection" requirement but you still will have an issue with strings. You need to ensure that you escape any characters that will make SQL injection possible, since strings will still be vulnerable to that when building up the SQL statement to be sent to the DB. I'm sure you can google how to do that (since I think that is a whole different discussion to your question).
So instead of newing up a new ReportParameter, you can have:
private ReportParameter GetReportParameter<T>(string name, T value)
{
return new ReportParameter
{
Name = name,
Value = value
};
}
Then when serializing:
var xml = Serialize(
new ReportParameters
{
Parameters = new ReportParameter[]
{
GetReportParameter("Test", 4),
GetReportParameter("Startdate", new DateTime(2019, 04, 05, 22, 52, 25))
}
});
Now, you are eliminating the need of the different class types of ReportParameterOfInt32 and ReportParameterOfDateTime.
As for wanting to deserialize the XML, you might need to add a separate Type field in your ReportParameter if you intent to store any Report designer info. However, I don't know exactly what your requirements are, but it might help you at runtime to determine the Type of the Value property in a simpler way.
UPDATE
@Patrick B has discovered that one can use XmlArrayItem to specify a Generic Type version of ReportParameter to accomplish this. Makes sense.
Example:
public class ReportParameters
{
[XmlArrayItem(Type = typeof(ReportParameter<int>))]
[XmlArrayItem(Type = typeof(ReportParameter<int?>))]
[XmlArrayItem(Type = typeof(ReportParameter<DateTime>))]
public List<ReportParameter> Parameters { get; set; } = new List<ReportParameter>();
}