How to use same query to insert data

Question

I am new at programming.

I am trying to create a simple guestbok. i have one index page where you can register a firstname, lastname and email.

And if you click on one name you redirect to a new page with id.

How can i now insert text to this ID with the same codeblock using the ID.

My code looks like this.

<?php
    require('dbconfig.php');

try {
        $conn = new PDO("mysql:host=$servername;dbname=projektone", $username, $password);
         //Set PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // Insert to database
        $sql = "INSERT INTO user (firstname, lastname, email)
        VALUE ('".$_POST["first_name"]."','".$_POST["last_name"]."','".$_POST["email"]."')";
        $sql = "INSERT INTO user (guestbok)
        VALUE ('".$_POST["guestbok"]."')";

        $conn->query($sql);
        }

        catch(PDOException $e)
        {
            echo $e->getMessage();
        }

header('Location: /');
?>

Thanks in advance

/Daniel

Just DO NOT use the same query. You have two queries, so run them in order. As simple as that. — Your Common Sense, Feb 17 '17 at 16:00
Your code is [wide open to SQL injection](http://stackoverflow.com/documentation/php/5828/pdo/2685/preventing-sql-injection-with-parameterized-queries) — Machavity, Feb 17 '17 at 16:01
You have errrors ? ... wrong result ? . what you problem .? is not clear ... please update your question with a proper data sample — ScaisEdge, Feb 17 '17 at 16:01
Okey, I will fix the SQL injection bit but my problem is or more like, how would you guys make the code so I can use it on multiple places instead of creating a new file for inserting to "guestbook". With this code, I can insert the first query but not use the second one on my other page. — Daniel, Feb 17 '17 at 16:16
are you saying you want to UPDATE the existing guestbook record? In which case you can't use the same query, because you need an UPDATE query. Or are you asking how to re-use this bit of code across different pages? In which case the answer is simple - same as how you re-use any program code, you put it into a function (which ideally, is inside a class), and put it into a file which you can then include whenever you need it (just like your dbconfig.php file) — ADyson, Feb 17 '17 at 16:28
you don't need the same code block. just put $conn = new PDO in to your dbconfig.php and then include it in any file where you need to run a query. then run it. — Your Common Sense, Feb 17 '17 at 16:28

score 1 · Answer 1 · answered Feb 17 '17 at 17:11

Joining up raw bits of text and passing them on to your database to process is not a good idea. It opens up your system to SQL injection. While it's unlikely that someone could compromise your site when only INSERT statements are exposed in this way, it does mean that:

anyone with an apostrophe in their name will break the logic of the request
you are exposing a method by which someone can carry out a stored XSS attack by submitting javascript to your guestbook

Regarding the SQL Injection problem, there are 2 methods to protect your system - one is to transform the data in which a way that it cannot break the SQL string it is added to (e.g. using mysqli_real_escape_string()) but the recommended approach when using PDO to mediate your code's interaction with the DBMS is to use variable binding. Here you compose your SQL command with placeholders for the data and substitute them at run time.

If your ID is generated from a mysql auto insert id, then you can read the value from $conn->lastinsertid

$stmt=$conn->prepare("INSERT INTO user (firstname, lastname, email)
    VALUES (:fnm,:lnm,:eml)");
$stmt->execute(array(
  ':fnm' => $_POST["first_name"],
  ':lnm' => $_POST["last_name"],
  ':eml' => $_POST["email"]));
$id=$conn->lastinsertid();

Your next problem is how to communicate this securely to the page where the user submits their guestbook comment (in your example code you try to do both operations in the same page).

Sending it in a round trip to the browser, as a cookie or as form variable means that it could be tampered with. There are esoteric stateless solutions where you can do this but with the data encrypted or cryptographically signed, however the simplest solution is to use sessions - add session_start() at the top of all your pages and any data you want available across requests can be stored in the $_SESSION superglobal.

(there are security issues relating to sessions as well)

When you receive the POST containing the guestbook data, then you should use an UPDATE user SET guestbook=:gstbk WHERE id=:id_from_session (or you could INSERT it into a seperate table with id as a foreign key)

Lastly, when you output the message the person left in your guestbook, make sure you protect the browser from any nasties in there:

print htmlentities($guestbook);

score 0 · Answer 2 · answered Feb 17 '17 at 16:33

Ok, probably I managed to get what you need. Put the following two lines in your dbconfig.php:

$conn = new PDO("mysql:host=$servername;dbname=projektone", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

and then require it wherever you need a database connection:

file one:

require('dbconfig.php');
$sql = "sql 1";
$conn->query($sql);

then in another file

require('dbconfig.php');
$sql = "sql 2";
$conn->query($sql);

How to use same query to insert data

2 Answers2