Using database metadata and domain membership for server-side validation of user-supplied inputs
Demonstrates a validation method that leverages database metadata for type checking, regular expression matching, and nullability checks, as well as authoritative domain-membership validation in an application's backend.
Validating user-supplied inputs in the backend of an application strengthens data integrity even if the frontend also performs validation. The challenge in performing server-side validation often lies in selecting validation methods appropriate to the business domain, constraints, and systems. A variety of techniques can be used: whitelisting/blacklisting, range and boundary checking, data type checking, regular expressions, reference/master data queries, or data dictionary lookups.
Another validation technique is metadata-driven validation based on the database's existing information schema or system catalog metadata. Since database metadata expresses structural constraints around table columns and routine parameters, it provides a logical source for retrieving validation specifications. Metadata-driven validation helps maintain parity between application validation rules and database constraints while reducing duplicate definitions of what a column or routine parameter will accept. It also provides a natural decision point for routing parameters toward either authoritative domain-membership validation or data-type validation.
Before implementing metadata-driven and domain membership validation, the SQL Server instance, client connectivity, and PHP SQL Server runtime dependencies must be configured.
Domain-Membership Validation
Domain-membership validation determines whether a user-supplied value exists within an authoritative set of reference or master data. Parameters are mapped in the application layer to a database routine that returns the corresponding membership values. The user-submitted value is then compared against the authoritative membership values by applying the matching semantics required by the parameter, such as exact equality or wildcard matching.
Domain-Membership Validation - Database
A simple sales schema is used in this post to demonstrate database metadata and domain-membership validation. The stored procedure used to retrieve sales orders exposes the parameter contract shown below. Parameter identity determines whether domain-membership validation or type validation is applied.
CREATE PROCEDURE [dbo].[usp_get_orders]
@pxCustomerName NVARCHAR ( 100 ) = NULL ,
@pxBusinessUnitCode CHAR ( 3 ) = NULL ,
@pxOrderStatusCode VARCHAR ( 12 ) = NULL ,
@pxSalesChannelCode CHAR ( 3 ) = NULL ,
@pxOrderDate DATE = NULL ,
@pxRequestedShipDate DATE = NULL ,
@pxRequestedShipDateOperator VARCHAR ( 16 ) = NULL ,
@pxMinTotalAmount DECIMAL ( 12 , 2 ) = NULL ,
@pxMaxTotalAmount DECIMAL ( 12 , 2 ) = NULL ,
@pxExpedited BIT = NULL
Domain-Membership Validation - Application Layer
In the application layer, the orchestrator preserves parameter identities consistent with the corresponding SQL Server routine. When the database operation is invoked, those application parameters are mapped to PDO named placeholders, which in turn map directly to the stored procedure parameters. This naming parity keeps the application-to-database contract explicit while avoiding ordinal-only parameter mapping.
/**
* getOrders
*
* Receives user-supplied CLI parameters, validates each parameter
* according to its domain-membership or type-validation contract,
* then returns the resulting orders data to the CLI.
*
* @return array
*/
function getOrders
(
string $pxCustomerName ,
string $pxBusinessUnitCode ,
string $pxOrderStatusCode ,
string $pxSalesChannelCode ,
string $pxOrderDate ,
string $pxRequestedShipDate ,
string $pxRequestedShipDateOperator ,
string $pxMinTotalAmount ,
string $pxMaxTotalAmount ,
string $pxExpedited
): array
{
$pxs = [
'pxCustomerName' => validatePx ( 'pxCustomerName' , $pxCustomerName ),
'pxBusinessUnitCode' => validatePx ( 'pxBusinessUnitCode' , $pxBusinessUnitCode ),
'pxOrderStatusCode' => validatePx ( 'pxOrderStatusCode' , $pxOrderStatusCode ),
'pxSalesChannelCode' => validatePx ( 'pxSalesChannelCode' , $pxSalesChannelCode ),
'pxOrderDate' => validatePx ( 'pxOrderDate' , $pxOrderDate ),
'pxRequestedShipDate' => validatePx (
'pxRequestedShipDate' ,
$pxRequestedShipDate ,
$pxRequestedShipDateOperator
),
'pxRequestedShipDateOperator' => validatePx ( 'pxRequestedShipDateOperator' , $pxRequestedShipDateOperator ),
'pxMinTotalAmount' => validatePx ( 'pxMinTotalAmount' , $pxMinTotalAmount ),
'pxMaxTotalAmount' => validatePx ( 'pxMaxTotalAmount' , $pxMaxTotalAmount ),
'pxExpedited' => validatePx ( 'pxExpedited' , $pxExpedited )
];
Parameters requiring domain-membership validation are identified separately in the application layer. Their parameter keys are maintained in an allowlist and routed to the domain-membership validation path before database execution.
/**
* Domain membership validation parameters.
*
* @var array<string>
*/
const REFDATA_PXS = [
'pxCustomerName' ,
'pxBusinessUnitCode' ,
'pxOrderStatusCode' ,
'pxSalesChannelCode'
];
// Validate domain membership p(x)'s.
if ( in_array ( $pxParameter , REFDATA_PXS , true ))
{
$validPx = filterDomainMembership (
$pxParameter ,
$validatePx
);
}
Type Validation
With domain-membership validation established, the remaining parameters can be validated against structural metadata exposed by SQL Server. This form of metadata-driven validation uses schema introspection to inspect the database routine’s parameter definitions and derive validation rules from the authoritative database contract.
Type Validation - Database
CREATE FUNCTION [dbo].[fx_get_metadata]
(
@pxSchema SYSNAME ,
@pxObjectName SYSNAME ,
@pxName VARCHAR (32)
)
RETURNS TABLE
AS
RETURN
(
SELECT
SPECIFIC_SCHEMA AS specific_schema,
SPECIFIC_NAME AS specific_name,
PARAMETER_NAME AS parameter_name,
ORDINAL_POSITION AS ordinal_position,
DATA_TYPE AS data_type,
CHARACTER_MAXIMUM_LENGTH AS character_maximum_length,
NUMERIC_PRECISION AS numeric_precision,
NUMERIC_SCALE AS numeric_scale
FROM INFORMATION_SCHEMA.PARAMETERS AS metadata_px
WHERE 1=1
AND metadata_px.SPECIFIC_SCHEMA = @pxSchema
AND metadata_px.SPECIFIC_NAME = @pxObjectName
AND metadata_px.PARAMETER_NAME = @pxName
);
The above function, dbo.fx_get_metadata, returns a metadata row for each parameter in a SQL Server routine. The PHP application layer consumes this resultset, using the data_type column to select the appropriate validation function. Character maximum length and numeric precision/scale provide the additional constraints needed to validate character and DECIMAL values. An example resultset is shown below.
Type Validation - Application Layer
The routine parameter metadata expresses the database's authoritative structural contract for acceptable values. The application layer consumes this metadata and routes each parameter to the appropriate type-validation function using a lookup structure analogous to the domain-membership routing list. The character and decimal filters shown below derive regular-expression constraints from the corresponding metadata and compare each user-supplied value against those constraints. When validation succeeds, the parameter value is returned; otherwise, false is returned. In this pattern, the filter functions both determine validity and return the accepted representation for continued processing. Character validation can be extended to account for database collation and encoding rules where the application domain requires it.
/**
* filterType
*
* @param string $pxParameter
* @param string $pxValue
*
* @return string|false
*/
function filterType ( string $pxParameter , string $pxValue ): string|false
{
$dbOperation = getDBOperation ( __FUNCTION__ );
$typeMetaData = executeQuery (
$dbOperation ,
[
'pxSchema' => 'dbo' ,
'pxObjectName' => 'usp_get_orders' ,
'pxName' => '@' . $pxParameter
]
)[0];
$filterDataType = $typeMetaData [ 'data_type' ];
$filterFx = TYPE_FXS [ $filterDataType ];
$filterType = $filterFx ( $pxValue , $typeMetaData );
return $filterType ;
}
/**
* filterCharacter
*
* @param string $pxValue
* @param array $pxTypeMetaData
*
* @return string|false
*/
function filterCharacter ( string $pxValue , array $pxTypeMetaData ): string|false
{
// Validate the input length against the metadata's maximum character length.
// Without the 'u' modifier, the regex operates on bytes.
$validationStringPrecision = $pxTypeMetaData [ 'character_maximum_length' ];
$pattern = '/^.{1,' . $validationStringPrecision . '}$/' ;
return ( \preg_match ( $pattern , $pxValue ) === 1 )
? $pxValue
: false ;
}
/**
* filterDecimal
*
* @param string $pxValue
* @param array $pxTypeMetaData
*
* @return string|false
*/
function filterDecimal ( string $pxValue , array $pxTypeMetaData ): string|false
{
// Derive integer precision from the metadata's total precision and scale.
// Build a regex that constrains the integer and fractional digit counts;
// The optional fractional portion uses a non-capturing group, '(?:...)'.
$numericPrecision = $pxTypeMetaData [ 'numeric_precision' ];
$numericScale = $pxTypeMetaData [ 'numeric_scale' ];
$integerPrecision = $numericPrecision - $numericScale ;
$pattern =
'/^-?\d{1,' . $integerPrecision . '}' .
'(?:\.\d{1,' . $numericScale . '})?$/' ;
return ( \preg_match ( $pattern , $pxValue ) === 1 )
? $pxValue
: false ;
}
Keeping Validation Aligned with Authoritative Sources