sql

Documentation

Index

• func BuildBitmaskCondition(inputStr, columnExpr, label string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildColumnInIntCondition(idStr string, expr string, label string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildContainedInColumnRangeCondition(inputStr, minCol, maxCol string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildGeographicRadiusCondition(lonStr, latStr, radiusStr, columnExpr string, startIndex int, ...) (int, error)
• func BuildInCondition(intStr, format, label string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildInConditionForStringSlice(inputStr, format, label string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildIntRangeCondition(minStr, maxStr, column string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildLikeConditions(inputStr, field string, argIndex int, conditions *[]string, ...) (int, error)
• func BuildLimitOffsetClause(limitStr, offsetStr string, startIndex int, args *[]interface{}) (string, int, error)
• func BuildSanitizedIlikeCondition(inputStr, columnExpr, label string, startIndex int, conditions *[]string, ...) (int, error)
• func BuildTimeCondition(timeStr, field, label string, argIndex int, conditions *[]string, ...) (int, error)
• func SanitizeSearchPattern(input string) (string, error)

Functions

func BuildBitmaskCondition

func BuildBitmaskCondition(
	inputStr, columnExpr, label string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildBitmaskCondition constructs a SQL bitmask filter from a string of flags.

It supports both a single integer value (e.g., "16") representing a full bitmask, and a comma-separated list of flag indices (e.g., "1,3,5"), which are combined into a single uint64 bitmask using bitwise OR (i.e., 1<<flag).

The resulting condition added to `conditions` has the form:

(columnExpr & $N) = $N

where $N is the placeholder for the bitmask value.

This format is commonly used to match rows where all specified flags are present in the column's stored bitmask value.

Parameters:

• inputStr: A string representing either a full bitmask (e.g., "16") or individual flag indices (e.g., "1,3,5").
• columnExpr: The SQL expression to test the bitmask against (e.g., "ed.accessibility_flags").
• label: A human-readable label for use in error messages (e.g., "accessibility_flags").
• startIndex: The current index for SQL placeholder numbering (e.g., 1, 2, 3, ...).
• conditions: A pointer to a slice of strings where the SQL condition will be appended.
• args: A pointer to a slice of interface{} where the corresponding SQL arguments will be appended.

Returns:

• The next available placeholder index after inserting the bitmask value.
• An error if any flag is invalid or out of the 0–63 range.

Example:

inputStr = "1,3,5" → bitmask = 1<<1 | 1<<3 | 1<<5 = 42
columnExpr = "ed.accessibility_flags"
Resulting SQL condition: "(ed.accessibility_flags & $1) = $1"
args = [42]

If inputStr is empty, the function does nothing and returns startIndex unchanged.

func BuildColumnInIntCondition

func BuildColumnInIntCondition(
	idStr string,
	expr string,
	label string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildColumnInIntCondition adds a SQL WHERE condition for a column to match one or more integer values.

Parameters:

• idStr: A comma-separated list of integer values (e.g., "1,2,3").
• expr: The SQL column or expression to match against (e.g., "user_id").
• label: A human-readable label used in error messages (e.g., "DbUser ID").
• startIndex: The current argument index for SQL placeholder numbering.
• conditions: A pointer to a slice of SQL condition strings to be appended to.
• args: A pointer to a slice of arguments to be passed to the SQL query.

Returns:

• newIndex: The updated argument index after processing.
• err: An error if parsing fails or idStr contains invalid integers.

func BuildContainedInColumnRangeCondition

func BuildContainedInColumnRangeCondition(
	inputStr, minCol, maxCol string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildContainedInColumnRangeCondition builds a condition to check if one or two integers are within a database range (minCol to maxCol), treating NULLs as defaults.

- One int: "($1 BETWEEN COALESCE(minCol, 0) AND COALESCE(maxCol, 1000))" - Two ints: same for each, combined with AND.

func BuildGeographicRadiusCondition

func BuildGeographicRadiusCondition(
	lonStr, latStr, radiusStr, columnExpr string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildGeographicRadiusCondition adds a PostGIS ST_DWithin condition to the given conditions slice, if all three input strings (lonStr, latStr, radiusStr) are valid and non-empty.

Parameters:

• lonStr, latStr, radiusStr: string inputs for longitude, latitude, and radius in meters.
• columnExpr: the SQL expression for the geometry column (e.g., "v.wkb_geometry").
• startIndex: the starting placeholder index for the SQL arguments.
• conditions: a pointer to the slice of WHERE conditions to append to.
• args: a pointer to the slice of SQL arguments.

Returns:

• nextArgIndex: the next available argument index after this condition.
• err: any error encountered while parsing the inputs.

func BuildInCondition

func BuildInCondition(intStr, format, label string, startIndex int, conditions *[]string, args *[]interface{}) (int, error)

BuildInCondition parses a comma-separated string of integers and appends a SQL IN condition to the provided conditions and args slices. It returns the updated SQL argument index.

Parameters:

• intStr: A comma-separated list of integers (e.g., "1,2,3").
• format: A SQL format string containing a single %s for the placeholder list.
• label: A label used for error messages.
• startIndex: The current SQL parameter index (e.g., 1 for $1).
• conditions: A pointer to the slice of condition strings to append to.
• args: A pointer to the slice of SQL arguments to append to.

Returns:

• newIndex: The next available argument index after this condition.
• err: An error if input parsing fails.

func BuildInConditionForStringSlice

func BuildInConditionForStringSlice(inputStr, format, label string, startIndex int, conditions *[]string, args *[]interface{}) (int, error)

BuildInConditionForStringSlice builds an SQL IN condition for a comma-separated string list. It also handles the transformation of the string list into an argument array for SQL queries.

Parameters:

• inputStr: A comma-separated string (e.g., "US, IN, CA") to be used in the IN condition.
• format: The format string for the SQL condition (e.g., "v.country_code IN (%s)").
• label: The label for the input parameter (e.g., "country_codes").
• startIndex: The starting index for SQL placeholders ($1, $2, ...).
• conditions: A pointer to the slice of conditions to append to.
• args: A pointer to the slice of arguments to append to.

Returns:

• The updated argument index after the condition is added.
• An error if any parsing issues occur (e.g., invalid formatting).

func BuildIntRangeCondition

func BuildIntRangeCondition(
	minStr, maxStr, column string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildIntRangeCondition parses min and max numeric string inputs and appends a SQL condition and arguments to filter a column by a numeric range (e.g., age BETWEEN min AND max).

Parameters:

• minStr: Minimum value as a string (e.g., "18").
• maxStr: Maximum value as a string (e.g., "65").
• column: The column name for the SQL condition (e.g., "age").
• startIndex: Starting index for SQL placeholders ($1, $2, ...).
• conditions: Pointer to a slice of WHERE clause conditions to be appended to.
• args: Pointer to a slice of arguments to be appended to.

Returns:

• newArgIndex: The updated argument index after adding new placeholders.
• err: An error if parsing of min/max values fails.

func BuildLikeConditions

func BuildLikeConditions(inputStr, field string, argIndex int, conditions *[]string, args *[]interface{}) (int, error)

BuildLikeConditions parses a comma-separated string of search patterns and appends an ILIKE condition to the provided slices for use in SQL WHERE clauses.

Parameters:

• inputStr: A comma-separated list of search terms (e.g., "art,design").
• field: The SQL column to apply the ILIKE condition to (e.g., "title").
• argIndex: The starting index for SQL placeholders ($1, $2, etc.).
• conditions: A pointer to a slice of condition strings (modified in place).
• args: A pointer to a slice of SQL argument values (modified in place).

Behavior:

• Wildcard `*` in inputStr will be converted to SQL wildcard `%`.
• Each value becomes a `field ILIKE $N` clause, joined by `OR`.
• The final condition is wrapped in parentheses: e.g., `(title ILIKE $1 OR title ILIKE $2)`.

Returns:

• The next available SQL argument index after all patterns.
• An error if input is malformed (currently always nil).

func BuildLimitOffsetClause

func BuildLimitOffsetClause(limitStr, offsetStr string, startIndex int, args *[]interface{}) (string, int, error)

BuildLimitOffsetClause parses limit and offset strings into integers, validates them, and appends the appropriate LIMIT and OFFSET clauses and arguments to the provided args slice.

Parameters:

• limitStr: Query string for limit (may be empty).
• offsetStr: Query string for offset (may be empty).
• startIndex: Index for SQL placeholders ($N).
• args: Pointer to the slice of arguments to be extended.

Returns:

• clause: A SQL clause like "LIMIT $1 OFFSET $2", or just one part.
• newIndex: The updated index after adding args.
• err: A validation error, if any.

func BuildSanitizedIlikeCondition

func BuildSanitizedIlikeCondition(
	inputStr, columnExpr, label string,
	startIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildSanitizedIlikeCondition sanitizes a search string and appends an ILIKE condition and corresponding argument to the provided slices.

Parameters:

• inputStr: The raw input string (e.g., from query param).
• columnExpr: SQL expression (e.g., "e.title" or "v.city").
• label: A descriptive name for error messages (e.g., "title", "city").
• startIndex: The index to start SQL placeholders at ($1, $2, ...).
• conditions: Pointer to slice of SQL conditions.
• args: Pointer to slice of SQL arguments.

Returns:

• newArgIndex: The next placeholder index after appending.
• error: If sanitization fails.

func BuildTimeCondition

func BuildTimeCondition(
	timeStr, field, label string,
	argIndex int,
	conditions *[]string,
	args *[]interface{},
) (int, error)

BuildTimeCondition parses a flexible time range string and appends a SQL BETWEEN condition to the provided conditions and args slices. It returns the updated argument index.

Parameters:

• timeStr: A comma-separated string with two times (e.g., "9,1730").
• field: The SQL field to apply the condition to.
• label: A human-readable label for error reporting.
• argIndex: The starting index for SQL parameter placeholders ($N).
• conditions: Pointer to the slice of condition strings to append to.
• args: Pointer to the slice of SQL arguments to append to.

Returns:

• newArgIndex: The next available argument index after appending.
• err: An error if input is invalid or cannot be parsed.

func SanitizeSearchPattern

func SanitizeSearchPattern(input string) (string, error)

SanitizeSearchPattern sanitizes a search pattern by handling wildcard characters and ensuring that the pattern is safe for use in SQL queries.

It processes the input string to:

• Escape literal asterisks (`\*`), replacing them with a placeholder before converting.
• Replace unescaped asterisks (`*`) with the SQL wildcard `%`.
• Restore literal asterisks from the placeholder back to `*`.
• Reject patterns with too many wildcards (e.g., "%%") or leading wildcards.

Parameters:

• input: The search pattern string that needs to be sanitized. This can include asterisks (`*`), which will be treated as SQL wildcards. If empty, it returns an empty string without error.

Returns:

• A sanitized version of the input string, where unsafe or invalid patterns are modified or rejected.
• An error if the sanitized pattern contains too many wildcards or any other dangerous patterns.

Errors:

• If the pattern contains too many `%` symbols (e.g., `%%` or patterns with leading wildcards), an error will be returned with the message "too many wildcards in city filter".

Example Usage:

sanitizedPattern, err := SanitizeSearchPattern("New*York")
if err != nil {
  // handle error
}