Prevent mysql injection in Php before submitted to the database?

Question

I have a client where their next auto-increment number just jumped from 2300 to ********** for reasons not understood. They want it set back.

Options such as dropping the primary key and rebuilding the index is NOT possible -- this is a relational table thing. So, is there a way (programmatically) to set the next number in an auto-increment?

Something like:

alter table abc auto_increment = 2301;

Any ideas of why this happened?

Answer 1

Use either PDO or Mysqli with the binding syntax. This alone will prevent most injection attacks.

Example:

 $stmt = $db->prepare(
                'UPDATE users ' .
                'SET userEmail=:email, userSalt=:salt, userPass=:pass ' .
                'WHERE userId=:userId LIMIT 1' );
    $stmt->bindParam( ':email',  $this->_email,    \PDO::PARAM_STR );
    $stmt->bindParam( ':salt',   $this->_salt,     \PDO::PARAM_STR );
    $stmt->bindParam( ':pass',   $this->_password, \PDO::PARAM_STR );
    $stmt->bindParam( ':userId', $this->_id,       \PDO::PARAM_INT );
    $stmt->execute();

In the above example, trying to escape the :email binding to insert a DROP TABLE won't work.

You still need to be careful with user-provided data. For instance, if the user provides a $docId for a get document query, make sure they're authorized for the document being requested. (And not just guessing a $docId belonging to some other user).