3

I am writing some MySQL procedures for a web-based application, and something that strikes me is that there is no argument's type check at all.

For instance, if I have the following :

CREATE PROCEDURE foo(n CHAR(4))

I can call it with whatever I want, it will accept it and only take the four first characters. But if I want to do something like this :

use base;
DELIMITER $$
DROP PROCEDURE IF EXISTS open $$

CREATE PROCEDURE open(n INT)
  BEGIN
    SELECT * FROM prod_charts LIMIT n;
  END $$

DELIMITER ;

It will just crash when called with a non-int parameter. And there is no feedback : when called from php, I just get nothing, and when I try it in phpMyAdmin, I am sent back to the home's page.

So my question is : how can I make it a little bit safer ? is there a way to check a variable's type in those procedures ?

Nathan Kleyn
  • 5,103
  • 3
  • 32
  • 49
user767435
  • 73
  • 4

1 Answers1

0

Unfortunately, I don't know of a way of doing this directly in mySQL. In other RDMS - like MS SQL, there are functions like isNumeric() that you can use.

Create the following function in mySQL and use it to check to see if the value passed in is numberic.

CREATE FUNCTION ISNUMERIC(myVal VARCHAR(1024)) 
RETURNS TINYINT(1) DETERMINISTIC 
RETURN myVal REGEXP '^(-|\\+)?([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

That should cover most (if not all) possibilities.

JohnL
  • 216
  • 1
  • 3