public void ValidSqlParameterized(string username) { var x = new SqlPhrase("Select top 1 from users where type = ") + "CUSTOMER".SqlStringConst() + (SqlPhrase)"AND role=" + 17 + (SqlPhrase)"AND username" + SqlComparison.Equal.For(username) + username; string actualSql = null; DynamicParameters actualParams = null; x.EmitParameterized((val, parms) => { actualSql = val; actualParams = parms; }); var expectedSql = username != null ? "Select top 1 from users where type = @p0 AND role= @p1 AND username = @p2" : "Select top 1 from users where type = @p0 AND role= @p1 AND username IS @p2"; Assert.Equal(expectedSql, actualSql); Assert.Equal("CUSTOMER", actualParams.Get <string>("p0")); Assert.Equal(17, actualParams.Get <int>("p1")); Assert.Equal(username, actualParams.Get <string>("p2")); }
// Special case term for RelativePath // as we want to return images not only in the relative path folder, but its subfolder as well. // Construct Partial Sql phrase used in Where for RelativePath that includes subfolders // Form: ( RelativePath='Station1\\Deployment2' OR RelativePath GLOB 'Station1\\Deployment2\\*' ) // or if relativePath is empty: "" public static string RelativePathGlobToIncludeSubfolders(string relativePathColumnName, string relativePath) { if (String.IsNullOrEmpty(relativePath)) { return(String.Empty); } // Form: ( DataTable.RelativePath='relpathValue' OR DataTable.RelativePath GLOB 'relpathValue\*' ) string term1 = SqlPhrase.DataLabelOperatorValue(relativePathColumnName, CustomSelection.TermToSqlOperator(Constant.SearchTermOperator.Equal), relativePath, false); string term2 = SqlPhrase.DataLabelOperatorValue(relativePathColumnName, CustomSelection.TermToSqlOperator(Constant.SearchTermOperator.Glob), System.IO.Path.Combine(relativePath, "*"), false); return(Sql.OpenParenthesis + term1 + Sql.Or + term2 + Sql.CloseParenthesis); }
public void ValidSelect() { var t = new SqlPhrase("SELECT TOP 100 FIRSTNAME, [LASTNAME] FROM [USERS] ORDER BY LASTNAME"); }
// Create and return the query composed from the search term list public string GetFilesWhere() { int numberOfDateTimesSearchTerms = 0; string where = String.Empty; // Construct and show the search term only if that search row is activated // Form after the ForEach should be: // "" if nothing in it // WHERE a=b for single terme // WHERE a=b AND c=d ... for multiple terms foreach (SearchTerm searchTerm in this.SearchTerms.Where(term => term.UseForSearching)) { if (string.IsNullOrEmpty(where)) { // Because there is at least one search term, we will need the WHERE clause where += Sql.Where; } // We want to see how many DateTime search terms we have. If there are two, we will be 'and-ing them nt matter what. if (searchTerm.ControlType == Constant.DatabaseColumn.DateTime) { numberOfDateTimesSearchTerms++; } // check to see if the search should match an empty string // If so, nulls need also to be matched as NULL and empty are considered interchangeable. string whereForTerm; string dataLabel = (this.DetectionSelections.Enabled == true) ? Constant.DBTables.FileData + "." + searchTerm.DataLabel : searchTerm.DataLabel; // Check to see if the search and operator should match an empty value, in which case we also need to deal with NULLs if (String.IsNullOrEmpty(searchTerm.DatabaseValue) && searchTerm.Operator == Constant.SearchTermOperator.Equal) { // Form: ( dataLabel IS NULL OR dataLabel = '' ); whereForTerm = SqlPhrase.LabelIsNullOrDataLabelEqualsEmpty(dataLabel); } else { // Form: dataLabel operator "value", e.g., DataLabel > "5" Debug.Assert(searchTerm.DatabaseValue.Contains("\"") == false, String.Format("Search term '{0}' contains quotation marks and could be used for SQL injection.", searchTerm.DatabaseValue)); whereForTerm = SqlPhrase.DataLabelOperatorValue(dataLabel, TermToSqlOperator(searchTerm.Operator), searchTerm.DatabaseValue); if (searchTerm.ControlType == Constant.Control.Flag) { whereForTerm += Sql.CollateNocase; // so that true and false comparisons are case-insensitive } } // if there is a term in the query other than ' Where 'add either and 'And' or an 'Or' to it if (where != Sql.Where) { if (numberOfDateTimesSearchTerms == 2) { where += Sql.And; numberOfDateTimesSearchTerms = 0; } else { switch (this.TermCombiningOperator) { case CustomSelectionOperatorEnum.And: where += Sql.And; break; case CustomSelectionOperatorEnum.Or: where += Sql.Or; break; default: throw new NotSupportedException(String.Format("Unhandled logical operator {0}.", this.TermCombiningOperator)); } } } where += whereForTerm; } // If no detections, return the above if (GlobalReferences.DetectionsExists == false || this.DetectionSelections.Enabled == false) { return(where); } // Add the Detection selection terms // Form prior to this point: SELECT DataTable.* INNER JOIN DataTable ON DataTable.Id = Detections.Id // (and if a classification it adds: // INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID // There are four basic forms to come up as follows, which determines whether we should include add 'WHERE' // The first is a detection and uses the detection category (i.e., any category but All Detections) // - WHERE Detections.category = <DetectionCategory> GROUP BY ... // The second is a dection but does not use a detection category(i.e., All Detections chosen) // - GROUP BY ... // XXXX The third uses applies to both // - WHERE Detections.category = <DetectionCategory> GROUP BY ... // - GROUP BY... // Form: WHERE or AND/OR // Add Where if we are using the first form, otherwise AND bool addAndOr = false; if (string.IsNullOrEmpty(where) && this.DetectionSelections.AllDetections == false && this.DetectionSelections.EmptyDetections == false) { where += Sql.Where; } else { addAndOr = true; } // DETECTION, NOT ALL // FORM: // If its a detection: Detections.category = <DetectionCategory> // If its a classification: Classifications.category = <DetectionCategory> // Only added if we are using a detection category (i.e., any category but All Detections) if (this.DetectionSelections.AllDetections == false && this.DetectionSelections.EmptyDetections == false) { if (addAndOr) { // Choose whether to use And or Or switch (this.TermCombiningOperator) { case CustomSelectionOperatorEnum.And: where += Sql.And; break; case CustomSelectionOperatorEnum.Or: where += Sql.Or; break; default: throw new NotSupportedException(String.Format("Unhandled logical operator {0}.", this.TermCombiningOperator)); } } if (this.DetectionSelections.RecognitionType == RecognitionType.Detection) { // a DETECTION // FORM Detections.Category = <DetectionCategory> where += SqlPhrase.DetectionCategoryEqualsDetectionCategory(this.DetectionSelections.DetectionCategory); } else { // a CLASSIFICATION, // FORM Classifications.Category = <ClassificationCategory> where += SqlPhrase.ClassificationsCategoryEqualsClassificationCategory(this.DetectionSelections.ClassificationCategory); } } // Form: see below to use the confidence range // Note that a confidence of 0 captures empty items with 0 confidence i.e., images with no detections in them // For the All category, we really don't wan't to include those, so the confidence has been bumped up slightly(in Item1) above 0 // For the Empty category, we invert the confidence Tuple <double, double> confidenceBounds = this.DetectionSelections.ConfidenceThresholdForSelect; if (this.DetectionSelections.RecognitionType == RecognitionType.Detection && this.DetectionSelections.RankByConfidence == false) { // Detection. Form: Group By Detections.Id Having Max ( Detections.conf ) BETWEEN <Item1> AND <Item2> e.g.. Between .8 and 1 where += SqlPhrase.GroupByDetectionsIdHavingMaxDetectionsConf(confidenceBounds.Item1, confidenceBounds.Item2); } else if (this.DetectionSelections.RecognitionType == RecognitionType.Classification && this.DetectionSelections.RankByConfidence == false) { // Classification. Form: GROUP BY Classifications.classificationID HAVING MAX ( Classifications.conf ) e.g., BETWEEN 0.8 AND 1 // Note: we omit this phrase if we are ranking by confidence, as we want to return all classifications where += SqlPhrase.GroupByClassificationsIdHavingMaxClassificationsConf(confidenceBounds.Item1, confidenceBounds.Item2); } return(where); }
// Combine the search terms in searchTemrs using the termCombiningOperator (i.e. And or OR), and special cases in as needed. private string CombineSearchTermsAndOperator(IEnumerable <SearchTerm> searchTerms, CustomSelectionOperatorEnum termCombiningOperator) { string where = String.Empty; foreach (SearchTerm searchTerm in searchTerms) { // Basic Form after the ForEach iteration should be: // "" if nothing in it // a=b for the firt term // ... AND/OR c=d ... for subsequent terms (AND/OR defined in termCombiningOperator // variations are special cases for relative path and datetime string whereForTerm = String.Empty; // If we are using detections, then we have to qualify the data label e.g., DataTable.X string dataLabel = (this.DetectionSelections.Enabled == true) ? Constant.DBTables.FileData + "." + searchTerm.DataLabel : searchTerm.DataLabel; // Check to see if the search term is querying for an empty string if (String.IsNullOrEmpty(searchTerm.DatabaseValue) && searchTerm.Operator == Constant.SearchTermOperator.Equal) { // It is, so we also need to expand the query to check for both nulls an empty string, as both are considered equivalent for query purposes // Form: ( dataLabel IS NULL OR dataLabel = '' ); whereForTerm = SqlPhrase.LabelIsNullOrDataLabelEqualsEmpty(dataLabel); } else { // The search term is querying for a non-empty value. Debug.Assert(searchTerm.DatabaseValue.Contains("\"") == false, String.Format("Search term '{0}' contains quotation marks and could be used for SQL injection.", searchTerm.DatabaseValue)); if (dataLabel == Constant.DatabaseColumn.RelativePath || dataLabel == Constant.DBTables.FileData + "." + Constant.DatabaseColumn.RelativePath) { // Special case for RelativePath and DataTable.RelativePath, // as we want to return images not only in the relative path folder, but its subfolder as well. // Form: ( DataTable.RelativePath='relpathValue' OR DataTable.RelativePath GLOB 'relpathValue\*' ) string term1 = SqlPhrase.DataLabelOperatorValue(dataLabel, TermToSqlOperator(Constant.SearchTermOperator.Equal), searchTerm.DatabaseValue, false); string term2 = SqlPhrase.DataLabelOperatorValue(dataLabel, TermToSqlOperator(Constant.SearchTermOperator.Glob), searchTerm.DatabaseValue + @"\*", false); whereForTerm += Sql.OpenParenthesis + term1 + Sql.Or + term2 + Sql.CloseParenthesis; } else if (dataLabel == Constant.DatabaseColumn.DateTime) { // A special form for DateTime, as we only want to consider the Date portion of the DateTime whereForTerm = SqlPhrase.DataLabelDateTimeOperatorValue(dataLabel, TermToSqlOperator(searchTerm.Operator), searchTerm.DatabaseValue); } else { // Standard search term // Form: dataLabel operator "value", e.g., DataLabel > "5" whereForTerm = SqlPhrase.DataLabelOperatorValue(dataLabel, TermToSqlOperator(searchTerm.Operator), searchTerm.DatabaseValue, searchTerm.ControlType == Constant.Control.Counter); } if (searchTerm.ControlType == Constant.Control.Flag) { // Because flags can have capitals or lower case, we need to make the search case insenstive whereForTerm += Sql.CollateNocase; // so that true and false comparisons are case-insensitive } } // We are now ready to assemble the search term // First, and only if there terms have already been added to the query, we need to add the appropriate operator if (!String.IsNullOrEmpty(where)) { switch (termCombiningOperator) { case CustomSelectionOperatorEnum.And: where += Sql.And; break; case CustomSelectionOperatorEnum.Or: where += Sql.Or; break; default: throw new NotSupportedException(String.Format("Unhandled logical operator {0}.", termCombiningOperator)); } } // Now we add the actual search terms where += whereForTerm; } // Done. Return this portion of the where clause return(where); }
// Create and return the query composed from the search term list public string GetFilesWhere() { string where = String.Empty; // Collect all the standard search terms which the user currently selected as UseForSearching IEnumerable <SearchTerm> standardSearchTerms = this.SearchTerms.Where(term => term.UseForSearching && (term.DataLabel == Constant.DatabaseColumn.File || term.DataLabel == Constant.DatabaseColumn.Folder || term.DataLabel == Constant.DatabaseColumn.RelativePath || term.DataLabel == Constant.DatabaseColumn.DateTime || term.DataLabel == Constant.DatabaseColumn.ImageQuality || term.DataLabel == Constant.DatabaseColumn.UtcOffset || term.DataLabel == Constant.DatabaseColumn.DeleteFlag)); // Collect all the non-standard search terms which the user currently selected as UseForSearching IEnumerable <SearchTerm> nonstandardSearchTerms = this.SearchTerms.Where(term => term.UseForSearching).Except(standardSearchTerms); // Combine the standard terms using the AND operator string standardWhere = CombineSearchTermsAndOperator(standardSearchTerms, CustomSelectionOperatorEnum.And); // Combine the non-standard terms using the operator defined by the user (either AND or OR) string nonStandarWhere = CombineSearchTermsAndOperator(nonstandardSearchTerms, this.TermCombiningOperator); // Combine the standardWhere and nonStandardWhere clauses, depending if one or both of them exists if (false == String.IsNullOrWhiteSpace(standardWhere) && false == String.IsNullOrWhiteSpace(nonStandarWhere)) { // We have both standard and non-standard clauses, so surround them with parenthesis and combine them with an AND // Form: WHERE (standardWhere clauses) AND (nonStandardWhere clauses) where += Sql.Where + Sql.OpenParenthesis + standardWhere + Sql.CloseParenthesis + Sql.And + Sql.OpenParenthesis + nonStandarWhere + Sql.CloseParenthesis; } else if (false == String.IsNullOrWhiteSpace(standardWhere) && String.IsNullOrWhiteSpace(nonStandarWhere)) { // We only have a standard clause // Form: WHERE (standardWhere clauses) where += Sql.Where + Sql.OpenParenthesis + standardWhere + Sql.CloseParenthesis; } else if (String.IsNullOrWhiteSpace(standardWhere) && false == String.IsNullOrWhiteSpace(nonStandarWhere)) { // We only have a non-standard clause // Form: WHERE nonStandardWhere clauses where += Sql.Where + nonStandarWhere; } // If no detections, we are done. Return the current where clause if (GlobalReferences.DetectionsExists == false || this.DetectionSelections.Enabled == false) { return(where); } // Add the Detection selection terms // Form prior to this point: SELECT DataTable.* INNER JOIN DataTable ON DataTable.Id = Detections.Id // (and if a classification it adds: // INNER JOIN Detections ON Detections.detectionID = Classifications.detectionID // There are four basic forms to come up as follows, which determines whether we should add 'WHERE' // The first is a detection and uses the detection category (i.e., any category but All Detections) // - WHERE Detections.category = <DetectionCategory> GROUP BY ... // The second is a dection but does not use a detection category(i.e., All Detections chosen) // - GROUP BY ... // XXXX The third uses applies to both // - WHERE Detections.category = <DetectionCategory> GROUP BY ... // - GROUP BY... // Form: WHERE or AND/OR // Add Where if we are using the first form, otherwise AND bool addAndOr = false; if (string.IsNullOrEmpty(where) && this.DetectionSelections.AllDetections == false && this.DetectionSelections.InterpretAllDetectionsAsEmpty == false) { where += Sql.Where; } else { addAndOr = true; } // DETECTION, NOT ALL // FORM: // If its a detection: Detections.category = <DetectionCategory> // If its a classification: Classifications.category = <DetectionCategory> // Only added if we are using a detection category (i.e., any category but All Detections) if (this.DetectionSelections.AllDetections == false && this.DetectionSelections.InterpretAllDetectionsAsEmpty == false) { if (addAndOr) { where += Sql.And; } if (this.DetectionSelections.RecognitionType == RecognitionType.Detection) { // a DETECTION // FORM Detections.Category = <DetectionCategory> where += SqlPhrase.DetectionCategoryEqualsDetectionCategory(this.DetectionSelections.DetectionCategory); } else { // a CLASSIFICATION, // FORM Classifications.Category = <ClassificationCategory> where += SqlPhrase.ClassificationsCategoryEqualsClassificationCategory(this.DetectionSelections.ClassificationCategory); } } // Form: see below to use the confidence range // Note that a confidence of 0 captures empty items with 0 confidence i.e., images with no detections in them // For the All category, we really don't wan't to include those, so the confidence has been bumped up slightly(in Item1) above 0 // For the Empty category, we invert the confidence Tuple <double, double> confidenceBounds = this.DetectionSelections.ConfidenceThresholdForSelect; if (this.DetectionSelections.RecognitionType == RecognitionType.Detection && this.DetectionSelections.RankByConfidence == false) { // Detection. Form: Group By Detections.Id Having Max ( Detections.conf ) BETWEEN <Item1> AND <Item2> e.g.. Between .8 and 1 where += SqlPhrase.GroupByDetectionsIdHavingMaxDetectionsConf(confidenceBounds.Item1, confidenceBounds.Item2); } else if (this.DetectionSelections.RecognitionType == RecognitionType.Classification && this.DetectionSelections.RankByConfidence == false) { // Classification. Form: GROUP BY Classifications.classificationID HAVING MAX ( Classifications.conf ) e.g., BETWEEN 0.8 AND 1 // Note: we omit this phrase if we are ranking by confidence, as we want to return all classifications where += SqlPhrase.GroupByClassificationsIdHavingMaxClassificationsConf(confidenceBounds.Item1, confidenceBounds.Item2); } return(where); }