How can I get this query in correct format?

Question

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.

Don't build queries with string concatenation. Use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Jun 02 '17 at 19:58
Mixing APIs here, that doesn't work. Your query isn't that big or complex that you need to build it dynamically. You can, of course, but at least build it with prepared statements. — Qirel, Jun 02 '17 at 21:21

Qirel · Answer 1 · 2017-06-07T11:40:35.030

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).

score 0 · Answer 2 · answered Jun 02 '17 at 20:05

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!

score 0 · Answer 3 · answered Jun 02 '17 at 20:05

0

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.

answered Jun 02 '17 at 20:05

Ravinder Reddy

3,869
1
13
22

score 0 · Answer 4 · answered Jun 07 '17 at 12:20

$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);
}

How can I get this query in correct format?

4 Answers4