php/sql - insert same value on multiple field

Question

I have this code (this is working and pass these variable to another file)

            var month = "<?php echo openedMonthbid();?>";
    var user = "<?php echo $_SESSION['member_id'];?>";
    var day = new Array();


    $(':checkbox:checked').each(function(i){
    day.push('`' + $(this).val() + '`');  });
    var count = day.length;

                            $.ajax({
                            type: "POST",
                            url: "sendBidding.php",
                            data : "user="+user+"&days="+day+"&month="+month+"&number=",
                            dataType: "json",

sendBidding.php

$month = $_POST['month'];
$user = $_POST['user'];
$days = $_POST['days'];
$count = $_POST['count'];//if a check 3 values I get '3'


      mysql_query("INSERT INTO $month ($days) VALUES ('1','1','1')");


    $result = true;

    echo json_encode(array("success"=>$result,
                               "datas" => $data,
                                "mon"=>$month));

I would like to add as many values ('1') as the number of days selected. How can I change VALUES ('1','1','1') ?

Your code completely lacks proper escaping of SQL. It is dangerous, because you are using the data from $_POST not as a value (which can be escaped with mysql_real_escape_string()), but as the names for tables and columns, for which no pre-made escaping function is available. Prepared statements would fail here, too. Go a different route! — Sven
– Sven, Commented Feb 3, 2013 at 19:55

Bill Karwin · Accepted Answer · 2013-02-04 01:26:28Z

2

Here's a solution for generating a sequence of identical strings. Use array_fill().

$month = $_POST['month'];
$days = $_POST['days'];

// Be sure to whitelist $month and $days before using them in an SQL query!  
// For example, you could store an associative array keyed by month,
// containing lists of the day column names.
$month_table_whitelist = array(
  "month_jan" => array("day1", "day2", "day3", /* etc */),
  "month_feb" => array("day1", "day2", "day3", /* etc */),
  /* etc */
);
if (!array_key_exists($month, $month_table_whitelist)) {
  die("Please specify a valid month.");
}
if (!array_search($days, $month_table_whitelist[$month])) {
  die("Please specify a valid day of month.");
}

$count = $_POST['count'];//if a check 3 values I get '3'

$tuples = array_fill(1, $count, "('1')");

$status = mysql_query("INSERT INTO $month ($days) VALUES ".implode(",", $tuples));
if ($status === false) {
  die(mysql_error());
}

PS: Your query is vulnerable to SQL injection, by interpolating unsafe values $month and $days directly into your query. You should use a whitelist method to ensure these inputs match real table and column names in your database, don't just trust the user input.

PPS: You should know that you're using the ext/mysql functions, but these are deprecated. If this is a new application, you should start using mysqli or PDO before investing more time into using the deprecated API.

edited Feb 4, 2013 at 1:26

answered Feb 3, 2013 at 19:57

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

7 Comments

Mat Over a year ago

I know, i'm still learling and I've been lazy. I will start to change that tomorrow. Ps I tried your code but when I check the database, there is no record.

Bill Karwin Over a year ago

So it failed silently, but we'll never know the cause unless we check for an error return status from the mysql_query() function. I have added that to the code above.

Sven Over a year ago

Escaping or sanitization for $month and $days is missing. This code is open for SQL injection.

Bill Karwin Over a year ago

@Sven, yes, I had addressed that issue with a comment, but I did not show an implementation of a whitelist. I have edited the above with an example. Please reverse your downvote.

Mat Over a year ago

@BillKarwin I think I found the error. if I leave $tuples = array_fill(1, $count, "('1')") the query doesn't work but if I change $count with a number (like 3) it does work. Is it possible that $count is just the string 3 and not the number 3 and that's why array_fill() doesn't work?

|

Jimzie · Accepted Answer · 2013-02-03 20:10:41Z

-1

$count = $_POST['count'];//if a check 3 values I get '3'

$daystatic="('1')";

mysql_query("INSERT INTO $month ($days) VALUES ". $daystatic . str_repeat ( ",$daystatic" , $count-1));

answered Feb 3, 2013 at 20:10

Jimzie

7375 silver badges6 bronze badges

1 Comment

Sven Over a year ago

Escaping or sanitization for $month and $days is missing. This code is open for SQL injection.

Collectives™ on Stack Overflow

php/sql - insert same value on multiple field

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related