SQL delete, and database / PHP logic

[alicedavidson]

Hi

 

I am trying to delete a row based on a user selection.

 

I have a bit of code here that is giving me "SQL Insertion error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

 

I'm not quite sure whats going on but the I have a PHP form and a PHP function and I'm thinking my logic is incorrect.

 

User selects non primary key field from drop down box, submits. Passes the primary key, and other fields to the delete_total function.

 

function delete_total($meid, $mid, $total, $comments, $ename) {
        $esc_meid = mysql_real_escape_string($meid, $this->conn);
        $esc_mid = mysql_real_escape_string($mid, $this->conn);
        $esc_total = mysql_real_escape_string($total, $this->conn);
        $esc_comments = mysql_real_escape_string($comments, $this->conn);
              $esc_ename = mysql_real_escape_string($ename, $this->conn);
        $sql = "DELETE FROM memberevent WHERE meid = $meid";
       $result = mysql_query($sql, $this->conn);
       
        if (!$result) {
            die("SQL Insertion error: " . mysql_error());
        } else {
            $numofrows = mysql_affected_rows($this->conn);
            return $numofrows;
        }
    }

Now, should I be actually selecting the PRIMARY key on the selection form, and not another field? I selecting a non primary key field because on a drop down 'meid' makes less sense to the user then the 'name' .

 

 

mysql v 5.1.41

[PFMaBiSmAd]

The error means that $meid in the query was empty. You would need to troubleshoot the code that is calling that method to find out why that parameter doesn't have a value.

 

Also, mysql_real_escape_string() is only useful for escaping string data that is put into a query (i.e. data that is surrounded by single-quotes in the query.) Since the parameter you are using is a number, you either need to validate it as a number or cast it as a number to prevent sql injection (a hacker can form sql to inject that doesn't involve using any quotes in it and if you were actually putting the output from your mysql_real_escape_string() function call into your query a hacker could inject sql with your current code.)