PHP - MySQL Transaction execution error

Question

    $tran = "START TRANSACTION;";  

    $tran_res = mysql_query($tran);  

    $qry_1 = "INSERT INTO docList (doc_ip , doc_country , doc_ref) VALUES ('$ip' , '$country' , '$http_ref');";  
    $res_1 = mysql_query($qry_1);  
    if(!$res_1)
        die ("qry1 fail " . mysql_error() );  

    $ins_id = mysql_insert_id();  
    if(!$ins_id) 
        die ("ins id fail " . mysql_error() );  
    echo "<b>$ins_id</b>";  

    $qry_2 = "INSERT INTO docContent (doc_id , cont_date , cont_title , cont_aim , cont_obj , cont_theory , cont_sw , cont_code) VALUES ('$ins_id' , '$dt' , '$title' , '$aim' , '$obj' , '$th' , '$sw' , '$code');"; 

    $res_2 = mysql_query($qry_2);  
    if(!$res_2)
        die("qry2 fail " . mysql_error() );  `

The execution of above is returning the following error:

2 qry fail 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 'login'); if($query->num_rows()>0) return $query->result_array(); } ' at line 1

In effect the execution of $qry_2 is failing, but I am perplexed by the error it is showing (there is no such code on line 1 as mentioned in the error note). Moreover, the query ($qry_2) executes properly in the MySql console.

Looks like you're not sanitizing your variables.

JJJ
– JJJ

2011-07-26 18:11:46 +00:00
Commented Jul 26, 2011 at 18:11 — JJJ
– JJJ, Commented Jul 26, 2011 at 18:11
@Juhana You are right! It working now.

AFV
– AFV

2011-07-26 18:21:37 +00:00
Commented Jul 26, 2011 at 18:21 — AFV
– AFV, Commented Jul 26, 2011 at 18:21

Marc B · Accepted Answer · 2011-07-26 18:14:01Z

2

Output the contents of $qry_2 to see the actual SQL statement be executed. Most likely you've got SQL injection vulnerabilities, and one of the variables you're inserting contains at least ' somewhere, causing the syntax error.

e.g. if you have

$var = "O'Reilly";
$sql = "INSERT INTO names (name) VALUES ('$var')";

you'll end up with

INSERT INTO names (name) VALUES ('O'Reilly');

which will be interpreted as:

'O' - string containing the letter "O"
Reilly - a field named "Reilly", with no operator between this "field" and the "O" previous
'); - a weird unterminated string, also with no operator between this and the previous field.

To get around this, you MUST pass your variables through mysql_real_escape_string(), which will prevent such errors from occuring. It'll turn O'Reilly into O\'Reilly, which is "safe" to use in your query.

answered Jul 26, 2011 at 18:14

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Álvaro González · Accepted Answer · 2011-07-26 18:14:14Z

1

You haven't posted the real query as received by the MySQL server, but I'd dare say you haven't used mysql_real_escape_string() to inject your data into your SQL.

(Are you trying to insert PHP code in the database?)

answered Jul 26, 2011 at 18:14

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

1 Comment

AFV Over a year ago

Yes the Code in the query can be from any language and of course it has been cured after 'sanitization' by use of mysql_escape_*

Collectives™ on Stack Overflow

PHP - MySQL Transaction execution error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related