Updating PostgreSQL database with PHP language

Question

I want to update the text information contained in my database for a specific layer. This layer is called via a WFS stream.

At first I displayed my layer with an Overlay with OpenLayers, and with a php script I want to update one of the data only according to the unique identifier of the object selected by the user.

To modify the data I created a text html object and a "submit" button

The html text object adds a new variable "maj_infos" which takes the value given by the user. And it uses the variable "feature-id" created by the Overlay which is displayed in text in the table.

I put you all the parts of my script:

<div class="bloc3">
<h3>Informations Signalétique</h3>
    <div class="overlay-container" >
    <table>
        <tr class="tr1">
            <td> Id
            </td>
            <td> Code Insee
            </td>
            <td> Année
            </td>
            <td> Sentier
            </td>
            <td> Infos
            </td>
        </tr>
        <tr>
            <td id="feature-id">
            </td>
            <td id="feature-insee">
            </td>
            <td id="feature-annee">
            </td>
            <td id="feature-sentier">
            </td>
            <td id="feature-infos">
            </td>
        </tr>
        </table>
    <h3>Modifier les informations de l'objet "Signalétique"</h3>
    <form method=POST >
        <input class="commentaire" type="text" id="maj_infos" name="maj_infos" required maxlength='140'/><br>
        <input type="submit" value="Envoyer" />
        <input type="hidden" id="feature-id" />
        <?php
            $dbconn = pg_connect("host=localhost dbname=xxxx user=xxxx password=xxxx")
            or die('Connexion impossible : ' . pg_last_error());
                                
            if (isset ($_POST['maj_infos']) && isset ($_GET['feature-id'])){
                $sql='UPDATE signaletique SET infos="'.$_POST['maj_infos'].'"WHERE id="'.$_GET['feature-id'].'"';
                                
            pg_query ($sql) or die ('Erreur SQL'.$sql.pg_error());
                                
                                
                echo 'Mise à jour des informations:'($_POST['maj_infos']);
            }
            else {
                echo 'Les variables ne sont pas déclarées';
            }
            pg_close();
        ?>
    </form>
    </div>
</div>

I also put a capture of the rendering.

I want the added information to update in the info column of my database. And since it is a WFS feed, my table contained on the page will also update.

I don't know php but unfortunately I have to use it. I don't understand where my error is in the php script. When I launch "submit" nothing happens no error message appears. Whereas until now there were.

Answer 1

The line $sql='UPDATE signaletique SET infos="'.$_POST['maj_infos'].'"WHERE id="'.$_GET['feature-id'].'"'; is extremely bad. Seriously.

Let's start with the gentle item: in Postgres, double quotes are used to specify an object name, in this case a field. The content of maj_infos is therefore interpreted as a field name, which likely does not exist.

Then the annoying item: you need to escape the string. If the given content contains a quote, it "closes" the string and the remaining text will trigger an error: ... SET myfield = 'L'erreur etait trop bete';

And to finish, the worst part: never, ever, build an SQL query with user input by concatenation. Repeat again, NEVER DO THAT. It is prone to SQL injection. ex: maj_infos = "'trop tard' where 1=1; --"

You can research the bold text to find out how to overcome these issues.