CODEWITHSUNDEEP
phpsqlmysqliinsert
bbowesbo
bbowesbo

Reputation: 925

How can I get this query in correct format?

I've been trying to get some data to input to my sqli database using php..

Looking at the insert queries when I do it by GUI on PHPMyAdmin the variables need to be wrapped in single quotes..

This is how I am building the query so far:

$fields = array('`appName`' => $_POST[appName],
                    '`appDescription`' => $_POST[appDescription],
                    '`UploadDate`' => date("Y-m-d"),
                    '`appWebsite`' => $_POST[appWebsite]);
printarray($fields);

print "<br>";
print "<br>";

$columns = implode(", ",array_keys($fields));
$escaped_values = array_map('mysql_real_escape_string', array_values($fields));
$values  = implode(", ", $escaped_values);

$sql = "INSERT INTO `applist`.`apps` ($columns) VALUES ($values)";

print $sql;
print "<br>";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error";
}

This is giving me the query like so.. 

INSERT INTO `applist`.`apps` (`appName`, `appDescription`, `UploadDate`, `appWebsite`) 
VALUES (SDD, DDD, 2017-06-02, DDDD)

How do I get the values of the array wrapped in single quotes?

Any help appreciated.

Upvotes: 1

Views: 389

Answers (4)

symcbean
symcbean

Reputation: 48367

$columns = implode(", ",array_keys($fields)); $escaped_values = array_map('mysql_real_escape_string', array_values($fields));

Ouch. not good.

Kudos trying to escape the content, but you're going to come unstuck using this code if the data ever contains commas.

You've got the beginnings of a nice generic method for inserting data, consider:

function insert($db_conn, $table, $data)
{
   $ins_vals=array(); // note we write the transformed data to a new array
      // as we may be modifying the column names too
   foreach ($data as $key=>$val) {
       // we need to protect the column names from injection attacks as
       // well as the data hence:
       $quoted_key="`" . str_replace("`", "", $key) . "`";

       // next we create an appropriate representation of the data value
       if (is_null($val)) {
           $ins_vals[$quoted_key]="NULL"; // in SQL 'NULL' != NULL
       } else if (is_numeric($val)) {
           // nothing to change here
           $ins_vals[$quoted_key]=$val;   // no need to quote/escape
       } else {
           $ins_vals[$quoted_key]="'" 
               . mysqli_real_escape_string($dbconn, $val) 
               . "'";
       }
   }
   // then we stick the bits together in an SQL statement
   $cols=implode(",", array_keys($ins_vals));
   $vals=implode(",", $ins_vals);
   $sql="INSERT INTO $table ($cols) VALUES ($vals)";
   return mysqli_query($dbconn, $sql);
}

Upvotes: 0

Qirel
Qirel

Reputation: 26460

There are at least two errors that I can spot.

  • Lacking single-quotes around strings in the query
  • Mixing APIs (mysql_ and mysqli_ doesn't mix), you use mysql_real_escape_string()

Both errors are fixed by using prepared statements in MySQLi. This isn't a very complex query, and might as well be written statically, but if you want to write it dynamically like this, that's not an issue - if you are on PHP 5.6, you can use array unpacking (...). To generate the placeholders ?, we create an array with count($fields) number of elements, all with ? as values. This is done with array_fill(). Then we implode() it into place, like we did with the columns.

$fields = array('`appName`' => $_POST['appName'],
                '`appDescription`' => $_POST['appDescription'],
                '`UploadDate`' => date("Y-m-d"),
                '`appWebsite`' => $_POST['appWebsite']);
$columns = implode(", ",array_keys($fields));

$sql = "INSERT INTO `applist`.`apps` ($columns) VALUES (".implode(", ", array_fill(0, count($fields), '?')).")";

if ($stmt = $conn->prepare($sql)) {
    $stmt->bind_param(str_repeat("s", count($fields)), ...$fields);
    if ($stmt->execute())
        echo "New record created successfully";
    else 
        echo "Insert failed";
    $stmt->close();
} else {
    echo "Error";
}

This takes care of quoting strings and prevents SQL injection.

To get any errors you might encounter, usemysqli_error($conn) and/or mysqli_stmt_error($stmt). This will tell you exactly what went wrong.

You should also quote your indexes from the POST-array. PHP will figure it out, and turn appName into 'appName', but it'd generate notices if you are logging errors (as you should).

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 3879

You need to append single quotes to each array value.

Replace

$values  = implode(", ", $escaped_values);

with

$values = "'" . implode ( "', '", $escaped_values ) . "'";

You even append double quotes.

Upvotes: 0

Mohammad Hamedani
Mohammad Hamedani

Reputation: 3354

Because you using implode, So you can add quotes with it and add start and end quote in sql query:

$fields = array('`appName`' => $_POST[appName],
                    '`appDescription`' => $_POST[appDescription],
                    '`UploadDate`' => date("Y-m-d"),
                    '`appWebsite`' => $_POST[appWebsite]);
printarray($fields);

print "<br>";
print "<br>";

$columns = implode(", ",array_keys($fields));
$escaped_values = array_map('mysql_real_escape_string', array_values($fields));
$values  = implode("', '", $escaped_values); //add qoutes

$sql = "INSERT INTO `applist`.`apps` ($columns) VALUES ('$values')"; //add start and end qoutes

print $sql;
print "<br>";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error";
}

But it's not good solution and maybe error occurred for other queries! Use PDO is better than it!

Upvotes: 0

Related Questions