Data::Validate::MySQL - validate against MySQL data types
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));
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.
new - constructor for OO usage
new();
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.
None
Returns a Data::Validate::MySQL object
is_bit - is the value a valid bit field?
is_bit($value, [$size], [$raw]);
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.
The potential value to test.
Optional width of the field in bits. Defaults to 1.
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 the untainted value on success, undef on failure.
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]);
The TINYINT type is an integer with a range of -128-127, or 0-255 if it is unsigned.
Set to true to validate against the unsigned range of the type.
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);
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.
is_smallint - is the value a valid SMALLINT field?
is_smallint($value, [$unsigned]);
The SMALLINT type is an integer with a signed range of -32768 to 32767. The unsigned range is 0 to 65535.
is_mediumint - is the value a valid MEDIUMINT field?
is_mediumint($value, [$unsigned]);
The MEDIUMINT type is an integer with a signed range of -8388608 to 8388607. The unsigned range is 0 to 16777215.
is_int - is the value a valid INTEGER field?
is_int($value, [$unsigned]);
The INTEGER (or INT) type is an integer with a signed range of -9223372036854775808 to to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
is_bigint - is the value a valid BIGINT field?
is_bigint($value, [$unsigned]);
The BIGINT type is an integer with a signed range of -9223372036854775808 to to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
is_float - is the value a valid FLOAT field?
is_float($value, [$m], [$d], [$unsigned]);
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.
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.
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.
Set to true to restrict to positive values.
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]);
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.
is_decimal - is the value a valid DECIMAL field?
is_decimal($value, [$m], [$d], [$unsigned]);
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.
Optional mantisa limit. Defaults to 65 (max for MySQL versions > 5.0.5)
Option decimal limit. Defaults to 30.
The function will die if $m or $d are non-integers.
is_char - is the value a valid CHAR field?
is_char($value, $length);
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.
Length of the field in characters. Max is 255. See Notes below.
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);
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.
Length of the field in characters. Max is 65,535. See Notes below.
The function will die if $length is not an integer, or is outside of the 0-65,535 range.
is_binary - is the value a valid BINARY field?
is_binary($value, $length);
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.)
Length of the field in bytes. Max is 255. See Notes below.
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);
The VARBINARY is similar to VARCHAR, except that its length is specified in bytes, rather than characters. (also sorts differently).
Length of the field in bytes. Max is 65,535. See Notes below.
is_tinyblob - is the value a valid TINYBLOB field?
is_tinyblob($value);
The TINYBLOB is effectively a VARBINARY field with a maximum size of 255 bytes.
is_tinytext - is the value a valid TINYTEXT field?
is_tinytext($value);
The TINYTEXT is effectively a VARCHAR field with a maximum size of 255 characters.
is_blob - is the value a valid BLOB field?
is_blob($value);
a BLOB is a variable-length binary field with a maximum size of 2**16 -1 bytes.
is_text - is the value a valid TEXT field?
is_text($value);
A TEXT field is a variable-length binary field with a maximum size of 2**16 -1 characters.
is_mediumblob - is the value a valid MEDIUMBLOB field?
is_mediumblob($value);
a MEDIUMBLOB is a variable-length binary field with a maximum size of 2**24 -1 bytes.
Returns the ORIGINAL value on success, undef on failure. See notes below.
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);
a MEDIUMTEXT is a variable-length text field with a maximum size of 2**24 -1 characters.
is_longblob - is the value a valid LONGBLOB field?
is_longblob($value);
a LONGBLOB is a variable-length binary field with a maximum size of 2**32 -1 bytes.
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);
a LONGTEXT is a variable-length binary field with a maximum size of 2**32 -1 characters.
is_enum - is the value a valid ENUM field?
is_enum($value, @set);
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.
Array of valid values.
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);
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.
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.
Returns an array reference of untainted values on success, undef on failure.
Always returns undef if $value is undefined. You must handle NULL values on your own.
is_date - is the value a valid DATE field?
is_date($value);
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:
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.
The value to test.
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.
is_datetime - is the value a valid DATETIME field?
is_datetime($value);
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.
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.
is_timestamp - is the value a valid TIMESTAMP field?
is_timestamp($value);
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.
is_time - is the value a valid TIME field?
is_time($value);
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:
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.
is_year - is the value a valid YEAR field?
is_year($value);
YEAR fields store a 4-digit year in a single byte field. Range is 1901 to 2155. You can enter years in several formats:
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.
Richard Sonnen <sonnen@richardsonnen.com>.
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:
You can't have =items (as at line 99) unless the first thing after the =over is an =item
To install Data::Validate::MySQL, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Data::Validate::MySQL
CPAN shell
perl -MCPAN -e shell install Data::Validate::MySQL
For more information on module installation, please visit the detailed CPAN module installation guide.