implode to fix update sql

Question

I have this function

function updateDbRecord($db, $table, $carry, $carryUrl) {   
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $arr = array_intersect_key( $_POST, array_flip($fieldnames) ); #check if value is null otherwise do not INSERT
        }
      }

      $set = "";
      foreach($arr as $key => $v) {
        $val = is_numeric($v) ? $v : "'" . $v . "'";

        $set .= $key . '=' . $val . ', ';
      }
      $sql = sprintf("UPDATE %s SET %s WHERE id='%s'", $table, $set, $_POST['id']);
      mysql_query($sql);
      if ($carry == 'yes') {
        redirect($carryUrl.'?id='.$_REQUEST['id']);
      } else { echo "Done!"; }
      echo $sql;

}

It outputs for example: UPDATE projects SET project_name='123', project_bold='123', project_content='123', WHERE id='12'

The last comma before where is preventing it from working. Is there a way of avoiding this? Im aware of the function implode, however I am not sure how to employ it in this situation.

Please fix the SQL injection vulnerabilities, and consider switching to either the MySqli or PDO extensions to access your database (the MySql extension is obselete). See http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php — John Carter, Jul 01 '12 at 01:31

score 0 · Answer 1 · answered Jul 01 '12 at 01:25

0

Yes,

$sql = substr($sql,'',-1);

answered Jul 01 '12 at 01:25

Sablefoste

4,032
3
37
58

score 0 · Answer 2 · answered Jul 01 '12 at 01:26

0

I would use

$sql = rtrim($sql, ',');

Either that or instead of appending to a string, append to an array and use implode.

answered Jul 01 '12 at 01:26

Explosion Pills

188,624
52
326
405

I have been trying to implement this solution to no avail. Could you please give me an example within this context? For a previous function of insert i did it this way: $sql = sprintf('INSERT INTO %s (%s) VALUES ("%s")', $table, implode(', ', array_map('mysql_escape_string', array_keys($values))), implode('", "',array_map('mysql_escape_string', $values))); mysql_query($sql); – Alex Jul 01 '12 at 01:29
1

@Alex why not just use something like `PDO`? – Explosion Pills Jul 01 '12 at 01:35
Haven't learned it yet, and within the rhelm of this project I dont have time :( But when i do i will certainty read material on it! – Alex Jul 01 '12 at 01:37
1

@Alex honestly I think it will take you about 5 minutes of trying before you learn, and it will save you so much time afterwards! It is much simpler than you seem to think. – Explosion Pills Jul 01 '12 at 03:41

score 0 · Answer 3 · answered Jul 01 '12 at 01:46

function updateDbRecord($db, $table, $carry, $carryUrl) {   
    mysql_select_db($db) or die("Could not select database. " . mysql_error());
    $resultInsert = mysql_query("SHOW COLUMNS FROM " . $table . " WHERE Field NOT IN ('id')");
    $fieldnames=array();
      if (mysql_num_rows($resultInsert) > 0) {
        while ($row = mysql_fetch_array($resultInsert)) {
            $fieldnames[] = $row['Field'];
            $array = array_intersect_key( $_POST, array_flip($fieldnames) ); #check if value is null otherwise do not INSERT
        }
      }
      foreach ($array as $key => $value) {

                $value = mysql_real_escape_string($value); // this is dedicated to @Jon
                $value = "'$value'";
                $updates[] = "$key = $value";
            }
      $implodeArray = implode(', ', $updates);
      $sql = sprintf("UPDATE %s SET %s WHERE id='%s'", $table, $implodeArray, $_POST['id']);
      mysql_query($sql);

implode to fix update sql

3 Answers3