Passing the splitted values in a query

Question

< ? php

$array = array('name1', 'name2', 'name3');
$comma_separated = implode(",", $array);

echo $comma_separated;

mysql_query("INSERT INTO  uploadfile(UF_ID,UF_NAME,GENRE,CAT_ID,SUB_CAT_ID,TAG,DESCRIPTION)             VALUES('mysql_insert_id()','$comma_separated','$GENRE','1','1','$tag','$optionaldescription')") ? >

How can i send these values through my query.
The way i am forming my query is that fine.

Use an associative array and loop trough it appending his respective keys / values into 2 strings. — yoda, Sep 07 '11 at 20:46

score 2 · Accepted Answer · answered Sep 07 '11 at 21:04

I'd do the following:

<?php

$array = array('name1', 'name2', 'name3');

$first=true;
$comma='';
$comma_separated='';
foreach($array as $value)
{
    if($first)
    {
        $first=false;
        $comma=',';
    }
    $comma_separated .= $comma.mysql_real_escape_string($value);
}

$result =mysql_query("INSERT INTO uploadfile (UF_ID,UF_NAME,GENRE,CAT_ID,SUB_CAT_ID,TAG,DESCRIPTION) VALUES('".mysql_insert_id()."','{$comma_separated}','".mysql_real_escape_string($GENRE)."','1','1','".mysql_real_escape_string($tag)."','".mysql_real_escape_string($optionaldescription)."');");
if(!$result)
{
    die( mysql_error() );
}

?>

Take note of the use of mysql_real_escape_string(); this function escapes the input for SQL and protects you SQL injection. Also, if you had escaped the values earlier* I'd advice you to use interpolation in your SQL query string. Like this:

"'1', '1', '{$tag}'"

Not:

"'1', '1', '$tag'"

Notice that I've changed how mysql_insert_id() is used too. For the same reason.

* - Like I've done with $comma_separated

score 0 · Answer 2 · edited May 23 '17 at 11:55

0

This is vulnerable to SQL injection. Use PDO and prepared statements instead:

$st = $pdo->prepare("INSERT INTO " .
         "uploadfile(UF_NAME,GENRE,CAT_ID,SUB_CAT_ID,TAG,DESCRIPTION) " .
         "VALUES(?,?,'1','1',?,?)");
$st->execute();

You don't need to specify the ID of a primary, auto-numbered key.

edited May 23 '17 at 11:55

Community

1
1

answered Sep 07 '11 at 20:48

phihag

245,801
63
407
443

I would imagine that random `mysql_insert_id()` is not supposed to be there and should probably be either a null or just removed from the query entirely. At the very least it should be broken out of the string to actually call the function... – DaveRandom Sep 07 '11 at 20:52
@DaveRandom oops. Fixed by completely removing the ID. This key should be set by the database. Doing it in php guarantees consistency problems and is unnecessarily slow. – phihag Sep 07 '11 at 21:17
True and agreed, with the caveat that if he is inserting a record that is related to a record created by the previous insert, it *might* be a valid usage of it (although obviously if you're using PDO it no longer is). But, I would confidently guess that it is not needed and your new version is correct. – DaveRandom Sep 07 '11 at 21:22

Passing the splitted values in a query

2 Answers2