CODEWITHSUNDEEP
phpmysqlstored-procedurespdo
Josh Smith
Josh Smith

Reputation: 11

MySQL Stored Procedure parameter validation

I'm wondering what the best practice is for return validation errors from a MySQL Stored Procedure to a PHP application. I've seen both Signals and strings being used, but I'm looking for a way to automatically process errors so they can be mapped to the forms` field.

For example, if I have a registration form, I need to make sure that the email doesn't already exist in the system. So I need the Stored Procedure to return a key => value ex. "email" => "Duplicate entry for email"

I'm also thinking that it might not make sense to store the messages (that will be sent to the client) in the database. Going off the email example above, "email" => "1062", then inside the PHP application, have a map, 1062 => "$key already exists."

I would be nice to be able to keep the key => (value / error message) association so an AJAX response object could contain something like

// Http Status 400
{ "email": "Email already exists", "name": "Name is required" }

That way the key of email and name and be directly associated with their inputs of "name" and "email".

<form>
  <input name="name"/>
  <input name="email"/>
  <input type="submit" value="Submit"/>
</form>


<?php
  $sanitizedInput = $_POST; //Pretend this data is actually sanitized.
  $pdo = new PDO($dsn, $username, $password);
  $stmt = $pdo->prepare("CALL registerUser(?, ?)");
  $stmt->bindParam(1, $sanitizedInput["name"]);
  $stmt->bindParam(2, $sanitizedInput["email"]);

  #Whats the best method for processing?
  try{
      if($stmt->execute()){

      }else{

      }
  }catch(PDOException $e){
     //I want to prevent doing string comparisons if possible
     //Signal example
     //It seems like this step could be automated and reused.
     //For example, if we allow a user to update their email address,
     //we could have the string 'Duplicate entry for email' in the
     //codebase twice, and it seems like the process could be easily broken.
     if($stmt->errorInfo[2] == 'Duplicate entry for email'){
        return array('email' => 'Email already exists');
     }
  }
?>

#SQL
signal sqlstate = '45000' set message_text = 'Duplicate entry for email'

 create procedure registerUser(
   name varchar(50),
   email varchar(50)
 )
 begin
 declare emailExists int(0);

 select count(*) into emailExists from users where email = email;

 if emailExists = 0 then
     insert into users values(default, name, email);
 else
     //Return {email: 'Duplicate entry for email'}
 end

end

Upvotes: 1

Views: 3021

Answers (1)

Junior
Junior

Reputation: 11992

Why would you run the validation in a stored procedure? why not use php to validate the format first. Then the second check would be this query

SELECT 1 FROM emails where email = '[email protected]' LIMIT 1

if this query return 1 that mean the record exists?

if you want to use stored procedure which is an over kill here.

You can use SQLEXCEPTION HANDLER

something like this

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @err_sqlstate = RETURNED_SQLSTATE,
        @err_message = MESSAGE_TEXT,
        @err_num = MYSQL_ERRNO;

        SET @hasError = TRUE;
    END;

After running a query you can do something like this

IF(@hasError)

 SELECT CONCAT("[",
      GROUP_CONCAT(
           CONCAT("{message:'", @err_message,"'"),
           CONCAT(",sqlState:'",@err_sqlstate),"'}")
      )
 ,"]") AS jSON
END IF;

Upvotes: 2

Related Questions