CODEWITHSUNDEEP
sqldatabasejoin
oldmanvette
oldmanvette

Reputation: 5

mysql update for five tables

I have looked and looked, mostly at UPDATE with multiple tables. Once or twice I searched specificially with 5 tables. The examples mostly show only two tables.

When I run the code below I get this message: update for memret 1: 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 '(M.first = test, M.last = nine, M.address1 = 999 woodland, M.zip = 21122, M.emai' at line 5 From my research this happens to many. I have switched around the code numerous times. This is my latest stab at what might fly but it crashed with the same message as above.

This code is below followed by the mysql db record.

Help please!

$sql = "UPDATE membership AS M
        LEFT JOIN address2 AS A2 ON M.memno1 = A2.memno2
      LEFT JOIN contact AS Con ON M.memno1 = Con.memno3
        LEFT JOIN workers AS W ON M.memno1 = W.memno4
      LEFT JOIN comments AS Com ON M.memno1 = Com.memno5";
$sql.=" SET (M.first = $first, M.last = $last, M.address1 = $address1,";               
$sql.=" M.zip = $zip, M.email = $email, M.password = $password,";
$sql.=" M.secq = $secq,M.seca = $seca,";
$sql.=" A2.address2 = $address2,";
$sql.=" Con.home = $home, Con.cell = $cell, Con.work = $work,";
$sql.=" W.webhelp = $webhelp, W.locorg = $locorg, W.candasst = $candasst,";
$sql.=" W.loccam = $loccam, W.other = $other, W.otherexp = $otherexp,";
$sql.=" Com.comment = $comment) WHERE memno1=$memno";
$result = mysql_query($sql) or die("update for memret 1: ".mysql_error());

memno1 first last address1 zip email password secq seca memno2 address2 memno3 home cell work memno4 webhelp locorg candasst loccam other otherexp memno5 comment memno6 office first last address1 address2 zip 9 test nine 999 woodland 21122 [email protected] tn9999 house wreck 9 dump 9 1232224444 333556666 2223335555 9 yes yes ceo 9 test new side

Upvotes: 0

Views: 57

Answers (1)

michaelb958--GoFundMonica
michaelb958--GoFundMonica

Reputation: 4708

This is an SQL injection. If I read the error message correctly, $address1 is "999 woodland" which will not be treated correctly by the SQL parser.

Stop substituting raw variables into query strings. (And stop using mysql_* functions, too. They're deprecated.) A prepared statement will go a long way here.

// assumes an existing PDO database connection in $conn
// requires exception-handling code (PDOException)
// requires you to check that e.g. integer fields will be updated with integers
$sql = "UPDATE membership AS M
  LEFT JOIN address2 AS A2 ON M.memno1 = A2.memno2
  LEFT JOIN contact AS Con ON M.memno1 = Con.memno3
  LEFT JOIN workers AS W ON M.memno1 = W.memno4
  LEFT JOIN comments AS Com ON M.memno1 = Com.memno5
  SET (M.first = :first, M.last = :last, M.address1 = :address1,
       M.zip = :zip, M.email = :email, M.password = :password,
       M.secq = :secq, M.seca = :seca,
       A2.address2 = :address2,
       Con.home = :home, Con.cell = :cell, Con.work = :work,
       W.webhelp = :webhelp, W.locorg = :locorg, W.candasst = :candasst,
       W.loccam = :loccam, W.other = :other, W.otherexp = :otherexp,
       Com.comment = :comment) WHERE memno1 = :memno";
$query = $conn->prepare($sql);
$params = array(":first" => $first, ":last" => $last, ":address1" => $address1,
                ":zip" => $zip, ":email" => $email, ":password" => $password,
                ":secq" => $secq, ":seca" => $seca,
                ":address2" => $address2,
                ":home" => $home, ":cell" => $cell, ":work" => $work,
                ":webhelp" => $webhelp, ":locorg" => $locorg,
                ":candasst" => $candasst,
                ":loccam" => $loccam, ":other" => $other,
                ":otherexp" => $otherexp,
                ":comment" => $comment, ":memno" => $memno);
$did_we_succeed = $query->execute($params);

Upvotes: 1

Related Questions