Formatting text with PHP

Question

Is there a way I can format a list so I can insert it via SQL?

My current list format is test:test and I want to be able to format it so it will insert into my database.

INSERT INTO `test` (`user`, `file`) VALUES
('test', 'test'),
('test2', 'test2'),
('[email protected]', 'test3');

Is this possible by any chance?

<?
if($_POST['oldlist']){

$sql = "SELECT * FROM test WHERE user='$_POST[oldlist]'";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($result) > 0) { echo "exists";} else {

$sqlq = "INSERT INTO test (`user` ,`file`)
VALUES ('$_POST[oldlist]');";
$add = mysql_query($sqlq) or die(mysql_error());
if($add){echo "done";}else {echo "failed";}
}}
?>

Something by that means? I want to be able to post a list and have it insert into my database, but it has to be formatted first.

Answer 1

You've got a nasty SQL injection flaw in your code. Make sure you escape your values! Here's how I would suggest you escape your code and insert it into your database:

<?php
// Use isset to determine whether there is a value present
if(isset($_POST['oldlist'])){

 $oldlist = $_POST['oldlist'];
 // Escape your data. Makes sure someone inject code that can jack your DB
 $oldlist_esc = mysql_real_escape_string($oldlist);

 $sql = "SELECT * FROM test WHERE user='$oldlist_esc'";
 $result = mysql_query($sql) or die(mysql_error());
 if(mysql_num_rows($result) > 0)
  echo "exists";
 else {
  // I'll assume your data is line-delimited
  $oldlist = explode("\n", $oldlist);
  $new_oldlist = $oldlist;
  foreach($oldlist as $key=>$value) {
   $new_oldlist[$key] = explode(" ", $value, 1); // I'll assume each line is space-delimited

   // Escape the new data
   foreach($new_oldlist[$key] as $new_key=>$new_value)
    $new_oldlist[$kew_key] = mysql_real_escape_string($new_value);
  }
  $sqlq =    "INSERT INTO test (`user` ,`file`) VALUES ";

  // Build out the query
  $c = 0;
  foreach($new_oldlist as $value) {
   if($c++ > 0) $sqlq .= ',';
   $sqlq .= '("';
   $sqlq .= implode('","', $value);
   $sqlq .= '")';
  }

  $add = mysql_query($sqlq) or die(mysql_error());
  if($add)
   echo "done";
  else
   echo "failed";
 }
}

?>

It's fine to use variables inside strings (i.e.: "asdfasdf $myvar asdfasdf"), but remember to escape your data using mysql_real_escape_string. This keeps the user from injecting data like:

';DELETE FROM test WHERE true;--

which would effectively wipe your database. Hope this helps!

Answer 2

If your $_POST has some paired values inside an array, try exploding each of the elements of the array by ":", joining them with the proper syntax for a single insert, then join the whole array for the multiple insert:

<?
// Just added 'olduser' as it seemed to go with WHERE user=... , change to your needs
if($_POST['olduser'] && $_POST['oldlist']){

$sql = "SELECT * FROM test WHERE user='$_POST[olduser]'";
$result = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($result) > 0) { echo "exists";} else {

foreach ($_POST['oldlist'] as &$each)
{
    $each = explode(":",$each);
    $each = "('".join("','",$each)."')";
}

$_POST['oldlist'] = join(",",$_POST['oldlist']);

// no semi-colon at end
$sqlq = "INSERT INTO test (`user` ,`file`) VALUES $_POST[oldlist]";
$add = mysql_query($sqlq) or die(mysql_error());
if($add){echo "done";}else {echo "failed";}
}}
?>

Anyway,be careful with your statements, I won't keep it without sanitizing the queries even for local usage.