///############################################################
/// <summary>
/// Creates a SQL UPDATE statement based on the provided data.
/// </summary>
/// <param name="sTableName">String representing the table name to target.</param>
/// <param name="a_oColumns">ColumnDescription array where each index represents a single column to be represented within the statement.</param>
/// <returns>String value containing the requested SQL UPDATE statement.</returns>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>sTableName</paramref> contains macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> is null or contains no columns.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains one or more <c>ColumnName</c>'s containing macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains no columns defining the WHERE clause (columns defined as non-insertable/updateable).</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains no columns defined as insertable/updateable.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains one or more unreconized <c>Operator</c>s.</exception>
///############################################################
/// <LastUpdated>July 10, 2006</LastUpdated>
public static string Update(string sTableName, ColumnDescription[] a_oColumns)
{
string sWhereClause = "";
string sOperator;
string sReturn = "";
int i;
//#### If the caller passed in a_oColumns to traverse
if (a_oColumns != null && a_oColumns.Length > 0)
{
//#### If the passed sTableName is safe
if (IsUserDataSafe(sTableName))
{
//#### Traverse the passed a_oColumns
for (i = 0; i < a_oColumns.Length; i++)
{
//#### If the current .ColumnName is safe
if (IsUserDataSafe(a_oColumns[i].ColumnName))
{
//#### If this is an .IsInsertOrUpdateColumn
if (a_oColumns[i].IsInsertOrUpdateColumn)
{
//#### If the .Value is a null-string
if (a_oColumns[i].Value.Length == 0)
{
//#### Determine the current a_oColumns' .Operator and process accordingly
switch (a_oColumns[i].Operator)
{
case enumValueOperators.cnInsertNull: {
//#### Append the .ColumnName and the NULL .Value onto the sReturn value
sReturn += "," + a_oColumns[i].ColumnName + "=NULL";
break;
}
case enumValueOperators.cnInsertNullString: {
//#### Append the .ColumnName and the null-string .Value onto the sReturn value
sReturn += "," + a_oColumns[i].ColumnName + "=''";
break;
}
}
}
//#### Else there is a .Value to insert
else
{
//#### If we are supposed to .Quote(the)Value (i.e. - this is a string-based type)
if (a_oColumns[i].Quote)
{
//#### Append the .ColumnName and the FormatForSQL'd .Value onto the sReturn value
sReturn += "," + a_oColumns[i].ColumnName + "='" + FormatForSQL(a_oColumns[i].Value, false) + "'";
}
//#### Else this must be a numeric (or otherwise non-quotable) value
else
{
//#### Append the .ColumnName and the .Value onto the sReturn value
//#### NOTE: We do a FormatForSQL below in order to avoid security issues. If this is indeed a numeric (or otherwise non-quotable) value, then .Value will not be changed by FormatForSQL. If it is a malicious attack, it will fail (as will the statement probably).
sReturn += "," + a_oColumns[i].ColumnName + "=" + FormatForSQL(a_oColumns[i].Value, false);
}
}
}
//#### Else if this is a WHERE clause column of some sort
else if (a_oColumns[i].IsWhereClauseColumn)
{
//#### Determine the sOperator for the current a_oColumn
sOperator = TranslateOperator("Update", a_oColumns[i].Operator);
//#### If we are supposed to .Quote(the)Value (i.e. - this is a string-based type)
if (a_oColumns[i].Quote)
{
//#### Append the .ColumnName and the PadSQL'd .Value onto the sWhereClause
sWhereClause += " AND " + a_oColumns[i].ColumnName + sOperator + "'" + FormatForSQL(a_oColumns[i].Value, false) + "'";
}
//#### Else this must be a numeric (or otherwise non-quotable) value
else
{
//#### If the .Value .Is(a)Numeric
//#### NOTE: Since .IsNumeric traverses the sValue one character at a time (and therefore does not convert it into a numeric type), there is no issue with overflow errors
if (Data.Tools.IsNumeric(a_oColumns[i].Value))
{
//#### Append the .ColumnName and the .Value onto the sWhereClause
//#### NOTE: We do a PadSQL below in order to avoid security issues. If this is indeed a numeric value, then .Value will not be changed by PadSQL. If it is a malicious attack, it will fail (as will the statement probably).
sWhereClause += " AND " + a_oColumns[i].ColumnName + sOperator + FormatForSQL(a_oColumns[i].Value, false);
}
}
}
}
//#### Else the current .ColumnName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Update", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### If the sWhereClause was never set above, raise the error
if (sWhereClause.Length == 0)
{
Internationalization.RaiseDefaultError(g_cClassName + "Update", Internationalization.enumInternationalizationValues.cnDeveloperMessages_DbTools_WhereClauseRequired, "a_oColumns", "");
}
//#### Else if insertable/updatable columns were present
else if (sReturn.Length > 0)
{
//#### Coalesce the above created sReturn value and the determined sWhereClause (while removing its leading " AND ", hence begining at 5)
sReturn = "UPDATE " + sTableName + " SET " + sReturn.Substring(1) + " WHERE " + sWhereClause.Substring(5);
}
//#### Else no insertable/updateable bColumns(were)Present, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Update", Internationalization.enumInternationalizationValues.cnDeveloperMessages_DbTools_InsertUpdateColumnsRequired, "a_oColumns", "");
}
}
//#### Else the passed sTableName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Update", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### Else the passed a_oColumns is empty, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Update", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_ValueRequired, "a_oColumns", "");
}
//#### Return the above determiend sReturn value to the caller
return(sReturn);
}
///############################################################
/// <summary>
/// Creates a SQL DELETE statement based on the provided data.
/// </summary>
/// <param name="sTableName">String representing the table name to target.</param>
/// <param name="a_oColumns">ColumnDescription array where each index represents a single column to be represented within the statement.</param>
/// <returns>String value containing the requested SQL DELETE statement.</returns>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>sTableName</paramref> contains macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> is null or contains no columns.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains one or more <c>ColumnName</c>s containing macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains no columns defining the WHERE clause (columns defined as non-insertable/updateable).</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains one or more unreconized <c>Operator</c>s.</exception>
///############################################################
/// <LastUpdated>February 15, 2006</LastUpdated>
public static string Delete(string sTableName, ColumnDescription[] a_oColumns)
{
string sOperator;
string sReturn = "";
int i;
//#### If the caller passed in a_oColumns to traverse
if (a_oColumns != null && a_oColumns.Length > 0)
{
//#### If the passed sTableName is safe
if (IsUserDataSafe(sTableName))
{
//#### Traverse the passed a_oColumns
for (i = 0; i < a_oColumns.Length; i++)
{
//#### If the current .ColumnName is safe
if (IsUserDataSafe(a_oColumns[i].ColumnName))
{
//#### If this is a .IsWhereClauseColumn
if (a_oColumns[i].IsWhereClauseColumn)
{
//#### Determine the sOperator for the current a_oColumn
sOperator = TranslateOperator("Delete", a_oColumns[i].Operator);
//#### If we are supposed to .Quote(the)Value (i.e. - this is a string-based type)
if (a_oColumns[i].Quote)
{
//#### Append the .ColumnName and the PadSQL'd .Value onto the sReturn value
sReturn += " AND " + a_oColumns[i].ColumnName + sOperator + "'" + FormatForSQL(a_oColumns[i].Value, false) + "'";
}
//#### Else this must be a numeric (or otherwise non-quotable) value
else
{
//#### If the .Value .Is(a)Numeric
//#### NOTE: Since .IsNumeric traverses the sValue one character at a time (and therefore does not convert it into a numeric type), there is no issue with overflow errors
if (Data.Tools.IsNumeric(a_oColumns[i].Value))
{
//#### Append the .ColumnName and the .Value onto the sReturn value
//#### NOTE: We do a PadSQL below in order to avoid security issues. If this is indeed a numeric value, then .Value will not be changed by PadSQL. If it is a malicious attack, it will fail (as will the statement probably).
sReturn += " AND " + a_oColumns[i].ColumnName + sOperator + FormatForSQL(a_oColumns[i].Value, false);
}
}
}
}
//#### Else the current .ColumnName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Delete", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### If the sReturn was never set above, raise the error
if (sReturn.Length == 0)
{
Internationalization.RaiseDefaultError(g_cClassName + "Delete", Internationalization.enumInternationalizationValues.cnDeveloperMessages_DbTools_WhereClauseRequired, "a_oColumns", "");
}
//#### Else we have all the data we need to build the statement
else
{
//#### Coalesce the above created sReturn (while removing its leading " AND ", hence 5), storing the return value back into the sReturn value
sReturn = "DELETE FROM " + sTableName + " WHERE " + sReturn.Substring(5);
}
}
//#### Else the passed sTableName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Delete", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### Else the passed a_oColumns is empty, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Delete", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_ValueRequired, "a_oColumns", "");
}
//#### Return the above determiend sReturn value to the caller
return(sReturn);
}
/* '###############################################################
* '# Removes all _DisallowedUserStrings and _DisallowedUserWords (as defined in RendererCore.custom.asp) with "", returning the resulting string
* '###############################################################
* '# Last Updated: June 23, 2004
* Public Function CleanSQL(ByVal sValue)
* Dim i
*
* '#### Default the return value to the normalized sValue
* CleanSQL = Normalize(sValue)
*
* '#### Traverse aRD_DisallowedUserStrings, replacing each _DisallowedUserString as we go
* For i = 0 To UBound(aRD_DisallowedUserStrings)
* CleanSQL = Replace(CleanSQL, aRD_DisallowedUserStrings(i), "", 1, -1, vbTextCompare)
* Next
*
* '#### Traverse aRD_DisallowedUserWords, replacing each _DisallowedUserWord as we go
* For i = 0 To UBound(aRD_DisallowedUserWords)
* CleanSQL = Replace(CleanSQL, " " & aRD_DisallowedUserWords(i) & " ", " ", 1, -1, vbTextCompare)
* Next
* End Function
*/
//##########################################################################################
//# Public Build*Statement-related Functions
//##########################################################################################
///############################################################
/// <summary>
/// Creates a SQL INSERT statement based on the provided data.
/// </summary>
/// <param name="sTableName">String representing the table name to target.</param>
/// <param name="a_oColumns">ColumnDescription array where each index represents a single column to be represented within the statement.</param>
/// <returns>String value containing the requested SQL INSERT statement.</returns>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>sTableName</paramref> contains macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> is null or contains no columns.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains one or more <c>ColumnName</c>'s containing macilicious SQL statements.</exception>
/// <exception cref="Cn.CnException">Thrown when the passed <paramref>a_oColumns</paramref> contains no columns defined as insertable/updateable.</exception>
///############################################################
/// <LastUpdated>March 1, 2006</LastUpdated>
public static string Insert(string sTableName, ColumnDescription[] a_oColumns)
{
string sInsertValues = "";
string sReturn = "";
int i;
bool bColumnsPresent = false;
//#### If the caller passed in a_oColumns to traverse
if (a_oColumns != null && a_oColumns.Length > 0)
{
//#### If the passed sTableName is safe
if (IsUserDataSafe(sTableName))
{
//#### Traverse the passed a_oColumns
for (i = 0; i < a_oColumns.Length; i++)
{
//#### If the current .ColumnName is safe
if (IsUserDataSafe(a_oColumns[i].ColumnName))
{
//#### As long as this is an .IsInsertOrUpdateColumn
if (a_oColumns[i].IsInsertOrUpdateColumn)
{
//#### Flip the value of bColumnsPresent to true
bColumnsPresent = true;
//#### If the .Value is a null-string
if (a_oColumns[i].Value.Length == 0)
{
//#### Determine the current a_oColumns' .Operator and process accordingly
switch (a_oColumns[i].Operator)
{
case enumValueOperators.cnInsertNull: {
//#### Append the .ColumnName and the NULL .Value onto the sReturn value
sReturn += "," + a_oColumns[i].ColumnName;
sInsertValues += ",NULL";
break;
}
case enumValueOperators.cnInsertNullString: {
//#### Append the .ColumnName and the null-string .Value onto the sReturn value
sReturn += "," + a_oColumns[i].ColumnName;
sInsertValues += ",''";
break;
}
}
}
//#### Else there is a .Value to insert
else
{
//#### If we are supposed to .Quote(the)Value (i.e. - this is a string-based type)
if (a_oColumns[i].Quote)
{
//#### Append the .ColumnName onto the sReturn value and the FormatForSQL'd .Value onto sInsertValues
sReturn += "," + a_oColumns[i].ColumnName;
sInsertValues += ",'" + FormatForSQL(a_oColumns[i].Value, false) + "'";
}
//#### Else this must be a numeric (or otherwise non-quotable) value
else
{
//#### Append the .ColumnName onto the sReturn value and the FormatForSQL'd .Value onto sInsertValues
//#### NOTE: We do a FormatForSQL below in order to avoid security issues. If this is indeed a numeric (or otherwise non-quotable) value, then .Value will not be changed by FormatForSQL. If it is a malicious attack, it will fail (as will the statement probably).
sReturn += "," + a_oColumns[i].ColumnName;
sInsertValues += "," + FormatForSQL(a_oColumns[i].Value, false);
}
}
}
}
//#### Else the current .ColumnName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Insert", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### If insertable/updateable bColumns(were)Present
if (bColumnsPresent)
{
//#### Coalesce the above created sInsertValues back into the return value (while removing their leading ","s)
sReturn = "INSERT INTO " + sTableName + " (" + sReturn.Substring(1) + ") VALUES (" + sInsertValues.Substring(1) + ")";
}
//#### Else no insertable/updateable bColumns(were)Present, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Insert", Internationalization.enumInternationalizationValues.cnDeveloperMessages_DbTools_InsertUpdateColumnsRequired, "a_oColumns", "");
}
}
//#### Else the passed sTableName seems to contain macilious code, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Insert", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_MaliciousSQLFound, "", "");
}
}
//#### Else the passed a_oColumns is empty, so raise the error
else
{
Internationalization.RaiseDefaultError(g_cClassName + "Insert", Internationalization.enumInternationalizationValues.cnDeveloperMessages_General_ValueRequired, "a_oColumns", "");
}
//#### Return the above determiend sReturn value to the caller
return(sReturn);
}
