NAME

Data::Validate::MySQL - validate against MySQL data types

SYNOPSIS

  use Data::Validate::MySQL qw(is_int);
  
  die "That's not an unsigned integer!" unless defined(is_int($suspect, 1));

  # or as an object
  my $v = Data::Validate::MySQL->new();
  
  die "That's not an unsigned integer!" unless defined($v->is_int($suspect, 1));

DESCRIPTION

This module collects common validation routines to check suspect values against MySQL column types. For example, you can check to make sure your integer values are within range, your strings aren't too big, and that your dates and times look vaguely ISO-ish. Validating your values before trying to insert them into MySQL is critical, particularly since MySQL is very tolerant of bad data by default, so you may end up with useless values in your tables even if the database doesn't complain.

All functions return an untainted value if the test passes, and undef if it fails. This means that you should always check for a defined status explicitly. Don't assume the return will be true. (e.g. is_integer('0'))

The value to test is always the first (and often only) argument.

FUNCTIONS

    new - constructor for OO usage

      new();
    Description

    Returns a Data::Validator::MySQL object. This lets you access all the validator function calls as methods without importing them into your namespace or using the clumsy Data::Validate::MySQL::function_name() format.

    Arguments

    None

    Returns

    Returns a Data::Validate::MySQL object

    is_bit - is the value a valid bit field?

      is_bit($value, [$size], [$raw]);
    Description

    The BIT type is effectively a very small integer (in fact, prior to MySQL version 5.0.3, it was an alias for TINYINT.) You can specify how many bits it holds (1-64) when creating your table. The same size should be passed to this function. (Defaults to 1, as does MySQL.) The function will return the untainted integer value if it is an integer, and can be stored within the specified number of bits.

    If the $raw argument is true, the function will validate the supplied string as a raw bit set. i.e. '1011001'. This matches the post-5.0.3 behavior with the 'b' flag. i.e. b'1011001'. In this case, the only legal values are 0 and 1, and the length must be <= $size.

    Arguments
    $value

    The potential value to test.

    $size

    Optional width of the field in bits. Defaults to 1.

    $raw

    Options raw-mode specifier. Tells the function to validate $value as if it were a string representation of the binary bit field. (see above.)

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    WARNING: This function does not yet handle integer validation correctly for bit fields larger than the integer width on your platform (likely 32 bits.) This is a bug, but I have not yet had a chance to convert the code to use an arbitrary-width integer library. Raw validation will work correctly all the way up to 64 bits.

    The function will die if the $size field is outside of the range 1-64.

    The function will always return undef if $value is undefined or zero length. If you want to allow for NULL values you'll need to check for them in advance.

    is_tinyint - is the value a valid TINYINT field?

      is_tinyint($value, [$unsigned]);
    Description

    The TINYINT type is an integer with a range of -128-127, or 0-255 if it is unsigned.

    Arguments
    $value

    The potential value to test.

    $unsigned

    Set to true to validate against the unsigned range of the type.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_boolean - is the value a valid BOOLEAN field?

      is_boolean($value);
    Description

    The BOOLEAN (or BOOL) type is just a single-digit TINYINT. Valid values are the same as a signed TINYINT. MySQL has stated that they will support a true boolean type at some point in the future.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_smallint - is the value a valid SMALLINT field?

      is_smallint($value, [$unsigned]);
    Description

    The SMALLINT type is an integer with a signed range of -32768 to 32767. The unsigned range is 0 to 65535.

    Arguments
    $value

    The potential value to test.

    $unsigned

    Set to true to validate against the unsigned range of the type.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_mediumint - is the value a valid MEDIUMINT field?

      is_mediumint($value, [$unsigned]);
    Description

    The MEDIUMINT type is an integer with a signed range of -8388608 to 8388607. The unsigned range is 0 to 16777215.

    Arguments
    $value

    The potential value to test.

    $unsigned

    Set to true to validate against the unsigned range of the type.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_int - is the value a valid INTEGER field?

      is_int($value, [$unsigned]);
    Description

    The INTEGER (or INT) type is an integer with a signed range of -9223372036854775808 to to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

    Arguments
    $value

    The potential value to test.

    $unsigned

    Set to true to validate against the unsigned range of the type.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_bigint - is the value a valid BIGINT field?

      is_bigint($value, [$unsigned]);
    Description

    The BIGINT type is an integer with a signed range of -9223372036854775808 to to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

    Arguments
    $value

    The potential value to test.

    $unsigned

    Set to true to validate against the unsigned range of the type.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    is_float - is the value a valid FLOAT field?

      is_float($value, [$m], [$d], [$unsigned]);
    Description

    The FLOAT type is a floating point number with a theoretical range of -3.402823466E+38 to -1.175494351E-38, 0, 1.175494351E-38 to 3.402823466E+38. MySQL gets a little vague on when you'll genuinely see this range, since it is hardware-dependent. Your milage may vary.

    Arguments
    $value

    The potential value to test.

    $m

    Optional mantisa limit. If set, only this many digits will be allowed. If unset, or set to the empty string, the value will only be checked against the theoretical min/max.

    $d

    Option decimal limit. If set, only this many digits will be allowed to the right of the decimal point. If unset, or set to the empty string, the value will only be checked against the theoretical min/max.

    $unsigned

    Set to true to restrict to positive values.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    The function will die if $m or $d are non-integers, or are less than 1.

    is_double - is the value a valid DOUBLE field?

      is_double($value, [$m], [$d], [$unsigned]);
    Description

    The DOUBLE type is a floating point number with a theoretical range of 1.7976931348623157E+308 to -2.2250738585072014E-308, 0, 2.2250738585072014E-308 to 1.7976931348623157E+308. MySQL gets a little vague on when you'll genuinely see this range, since it is hardware-dependent. Your milage may vary.

    Arguments
    $value

    The potential value to test.

    $m

    Optional mantisa limit. If set, only this many digits will be allowed. If unset, or set to the empty string, the value will only be checked against the theoretical min/max.

    $d

    Option decimal limit. If set, only this many digits will be allowed to the right of the decimal point. If unset, or set to the empty string, the value will only be checked against the theoretical min/max.

    $unsigned

    Set to true to restrict to positive values.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    The function will die if $m or $d are non-integers, or are less than 1.

    is_decimal - is the value a valid DECIMAL field?

      is_decimal($value, [$m], [$d], [$unsigned]);
    Description

    The DECIMAL type is a fixed-point number that stores what would otherwise be floating point numbers "exactly." You specify the total number of digits and the total number of digits after the decimal point. As long as your number fits within that range, it will be stored exactly.

    Arguments
    $value

    The potential value to test.

    $m

    Optional mantisa limit. Defaults to 65 (max for MySQL versions > 5.0.5)

    $d

    Option decimal limit. Defaults to 30.

    $unsigned

    Set to true to restrict to positive values.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or zero-length. You must handle NULL values on your own.

    The function will die if $m or $d are non-integers.

    is_char - is the value a valid CHAR field?

      is_char($value, $length);
    Description

    The CHAR type is a fixed-size text field with a maximum character width of 255 characters. This test uses Perl's length() function to check the field width, so it should be compatible with multi-byte character sets.

    No attempt is made to check the range of the supplied characters, since interpreting them correctly would depend on knowledge of the character set. Maybe something to add down the road.

    Arguments
    $value

    The potential value to test.

    $length

    Length of the field in characters. Max is 255. See Notes below.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    The function will die if $length is not an integer, or is outside of the 0-255 range.

    Note that because we do not know much about the characters being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_varchar - is the value a valid VARCHAR field?

      is_varchar($value, $length);
    Description

    The VARCHAR type is a fixed-size text field with a maximum character width of 65,535 characters (post 5.0.3). For our purposes, this type is identical to CHAR (see is_char) other than the higher maximum size.

    Arguments
    $value

    The potential value to test.

    $length

    Length of the field in characters. Max is 65,535. See Notes below.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    The function will die if $length is not an integer, or is outside of the 0-65,535 range.

    Note that because we do not know much about the characters being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_binary - is the value a valid BINARY field?

      is_binary($value, $length);
    Description

    The BINARY type is identical to a CHAR, with the exception that the length of the field is in bytes, rather than characters. (also has differences in how they are sorted, but that's outside the concern of this function.)

    Arguments
    $value

    The potential value to test.

    $length

    Length of the field in bytes. Max is 255. See Notes below.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    The function will die if $length is not an integer, or is outside of the 0-255 range.

    Note that because we do not know much about the bytes being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_varbinary - is the value a valid VARBINARY field?

      is_varbinary($value, $length);
    Description

    The VARBINARY is similar to VARCHAR, except that its length is specified in bytes, rather than characters. (also sorts differently).

    Arguments
    $value

    The potential value to test.

    $length

    Length of the field in bytes. Max is 65,535. See Notes below.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    The function will die if $length is not an integer, or is outside of the 0-65,535 range.

    Note that because we do not know much about the characters being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_tinyblob - is the value a valid TINYBLOB field?

      is_tinyblob($value);
    Description

    The TINYBLOB is effectively a VARBINARY field with a maximum size of 255 bytes.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Note that because we do not know much about the bytes being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_tinytext - is the value a valid TINYTEXT field?

      is_tinytext($value);
    Description

    The TINYTEXT is effectively a VARCHAR field with a maximum size of 255 characters.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Note that because we do not know much about the bytes being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_blob - is the value a valid BLOB field?

      is_blob($value);
    Description

    a BLOB is a variable-length binary field with a maximum size of 2**16 -1 bytes.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Note that because we do not know much about the bytes being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_text - is the value a valid TEXT field?

      is_text($value);
    Description

    A TEXT field is a variable-length binary field with a maximum size of 2**16 -1 characters.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Note that because we do not know much about the bytes being supplied, we cannot really untaint the string in any meaningful way. Although the taint flag will be removed in the return, you should in no way consider it to be "safe."

    is_mediumblob - is the value a valid MEDIUMBLOB field?

      is_mediumblob($value);
    Description

    a MEDIUMBLOB is a variable-length binary field with a maximum size of 2**24 -1 bytes.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the ORIGINAL value on success, undef on failure. See notes below.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Because of the potential size of the field, this function does not attempt to untaint the value. (Doing so is effectively useless anyway, since we know nothing about the format of the data.)

    is_mediumtext - is the value a valid MEDIUMTEXT field?

      is_mediumtext($value);
    Description

    a MEDIUMTEXT is a variable-length text field with a maximum size of 2**24 -1 characters.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the ORIGINAL value on success, undef on failure. See notes below.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Because of the potential size of the field, this function does not attempt to untaint the value. (Doing so is effectively useless anyway, since we know nothing about the format of the data.)

    is_longblob - is the value a valid LONGBLOB field?

      is_longblob($value);
    Description

    a LONGBLOB is a variable-length binary field with a maximum size of 2**32 -1 bytes.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the ORIGINAL value on success, undef on failure. See notes below.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Because of the potential size of the field, this function does not attempt to untaint the value. (Doing so is effectively useless anyway, since we know nothing about the format of the data.)

    On a related note, these fields can be up to 4G. Passing a value of that size to this function may not be a great idea. Actually, storing a value that size in a single DB field may not be that great an idea.

    is_longtext - is the value a valid LONGTEXT field?

      is_longtext($value);
    Description

    a LONGTEXT is a variable-length binary field with a maximum size of 2**32 -1 characters.

    Arguments
    $value

    The potential value to test.

    Returns

    Returns the ORIGINAL value on success, undef on failure. See notes below.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own. An empty string is considered valid.

    Because of the potential size of the field, this function does not attempt to untaint the value. (Doing so is effectively useless anyway, since we know nothing about the format of the data.)

    On a related note, these fields can be up to 4G. Passing a value of that size to this function may not be a great idea. Actually, storing a value that size in a single DB field may not be that great an idea.

    is_enum - is the value a valid ENUM field?

      is_enum($value, @set);
    Description

    An ENUM field stores a fixed number of strings efficiently as an integer index. This function just checks to see if the test value occurs in the valid set.

    Note that prior to version MySQL 4.1.1, ENUM values were compared in a case-insensitive fashion. Post 4.1.1, ENUMs can be assigned a character set and collation, which may make them case sensitive. Since this function doesn't know your version or character set, it defaults to being case sensitive to be on the safe side.

    Arguments
    $value

    The potential value to test.

    @set

    Array of valid values.

    Returns

    Returns the untainted value on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined or empty. You must handle NULL values on your own.

    The untainting system is guaranteed to return a string identical to one in @set, so (assuming you can trust the origins of @set) you can trust the untainted value.

    This function turns the value set into a lookup hash each time it's called. If you have a very large enum set, or a large number of values to check, you may do better to roll your own check with a cached lookup hash.

    is_set - is the value a valid SET field?

      is_set(\@values, @set);
    Description

    SET fields are similar to ENUM fields in that they select their values from a predefined list that gets stored as an integer array index. However, SETs can have multiple values at the same time.

    Note that the empty set is always allowed in a SET, even if the column is declared as NOT NULL.

    Arguments
    \@values

    The potential values to test. Each member must exist in the @set array. An empty array is always considered valid. (returns an empty array as the 'untainted' value.)

    @values (like SETs) can have a maximum of 64 values.

    @set

    Array of valid values.

    Returns

    Returns an array reference of untainted values on success, undef on failure.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

    The untainting system is guaranteed to return a string identical to one in @set, so (assuming you can trust the origins of @set) you can trust the untainted value.

    This function turns the value set into a lookup hash each time it's called. If you have a very large enum set, or a large number of values to check, you may do better to roll your own check with a cached lookup hash.

    is_date - is the value a valid DATE field?

      is_date($value);
    Description

    DATE fields store year, month and day values from 1000-01-01 to 9999-12-31. They can be set using a wide variety of input formats:

    YYYY-MM-DD HH:MM:SS
    YY-MM-DD HH:MM:SS
    YYYY-MM-DD
    YY-MM-DD
    YYYYMMDDHHMMSS
    YYMMDDHHMMSS
    YYYYMMDD
    YYMMDD

    DATE fields simply ignore any time-related fields you may include.

    This function attempts to recognize and validate all the formats above, though it is currently fairly naive regarding ranges. (i.e. it won't stop you from having a day that can't exist in the month you've specified.) Future versions of this module may correct that.

    Arguments
    $value

    The value to test.

    Returns

    Unlike most other Data::Validate functions, this one returns the value in an untainted canonical (YYYY-MM-DD 00:00:00) format, regardless of the format you supplied. Invalid values return undef.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

    is_datetime - is the value a valid DATETIME field?

      is_datetime($value);
    Description

    DATETIME fields store year, month, day, hour, minute, and secod values from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

    See is_date() for possible formats and caveats.

    Arguments
    $value

    The value to test.

    Returns

    Unlike most other Data::Validate functions, this one returns the value in an untainted canonical (YYYY-MM-DD HH:MM:SS) format, regardless of the format you supplied. Invalid values return undef.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

    is_timestamp - is the value a valid TIMESTAMP field?

      is_timestamp($value);
    Description

    TIMESTAMP fields are similar to DATETIME as far as how they are set and displayed. They have other auto-updating behavior of course, but it doesn't have much bearing on validation.

    The major difference between the two is range - TIMESTAMPS are stored as UNIX timestamps, and so only have a range of 1970 to 2037.

    See is_date() for possible formats and caveats.

    Arguments
    $value

    The value to test.

    Returns

    Unlike most other Data::Validate functions, this one returns the value in an untainted canonical (YYYY-MM-DD HH:MM:SS) format, regardless of the format you supplied. Invalid values return undef.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

    is_time - is the value a valid TIME field?

      is_time($value);
    Description

    TIME fields seem to trip people up, since they think of them in terms of clock time (i.e. the HH:MM:SS component of a DATETIME for example.) However, they are really more a representation of elapsed time. (Which is why they can be greater than 24 hours, or even be negative.)

    Valid range is -838:59:59' to '838:59:59'. They can be specified in a number of different ways:

    D HH:MM:SS.fraction
    HH:MM:SS.fraction
    HH:MM:SS
    HH:MM
    D HH:MM:SS
    D HH:MM
    D HH
    HHMMSS
    HHMMSS.fraction
    MMSS
    SS
    Arguments
    $value

    The value to test.

    Returns

    Unlike most other Data::Validate functions, this one returns the value in an untainted canonical ([H]HH:MM:SS) format, regardless of the format you supplied. Invalid values return undef.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

    is_year - is the value a valid YEAR field?

      is_year($value);
    Description

    YEAR fields store a 4-digit year in a single byte field. Range is 1901 to 2155. You can enter years in several formats:

    YYYY
    YY
    Y
    Arguments
    $value

    The value to test.

    Returns

    Unlike most other Data::Validate functions, this one returns the value in an untainted canonical (YYYY) format, regardless of the format you supplied. Invalid values return undef.

    Notes, Exceptions, & Bugs

    Always returns undef if $value is undefined. You must handle NULL values on your own.

AUTHOR

Richard Sonnen <sonnen@richardsonnen.com>.

COPYRIGHT

Copyright (c) 2005 Richard Sonnen. All rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 91:

You can't have =items (as at line 99) unless the first thing after the =over is an =item