problems updating a table

2

I'm a beginner in php. does anyone know why it is not working? It does update correctly integer values, but if it is a string of letters does not work

html He will type here a url to do the update

<input type="text" id="url_video">

salva.php

$con = mysqli_connect("localhost","root","","database_name");

$resultado = mysqli_query($con,"Select * from video");

    echo "<table class='table'>".
        "<thead>".
             "<tr>".
                "<td>ID</td>".
                "<td>url_video</td>".
             "</tr>".
        "</thead><tbody>";
while($valor = mysqli_fetch_array($resultado)){
    echo "<tr>".
            "<td>".$valor['ID']."</td>".
            "<td>".$valor['url_video']."</td>".
            "<td><input type='button' value='Update' class='btn-update' data-id='".$valor['ID']."'>"."</td>".
         "</tr>";
}
echo "</tbody></table>";

ajax I send the typed string (url) and step the id of the selected column.

$(document).ready(function(){
    $('.btn-update').click(function(){
        var id = $(this).data('id');
        var string = $('#url_video').val();
        $.ajax({
            url: "tabelaUpdate.php",
            data: { 'idDeUpdate' : id,
                    'url_video' : string
                  },
            type: "POST",
            cache: false,
            success: function(response){
                $('#result').html(response); 
            }
        })
    });
});

php tableUpdate.php

$con = mysqli_connect("localhost","root", "", "database_name");
$id = $_POST['idDeUpdate'];
$string_url = $_POST['url_video'];


mysqli_query($con,"Update video SET url_video=$string_url WHERE ID=$id");
echo "ID -> ".$id." URL -> ".$string_url;
    
asked by anonymous 26.03.2015 / 06:26

1 answer

3

I would suggest changing this line:

"<td><input type='button' value='Update' class='btn-update' data-id='".$valor['ID']."'>"."</td>"

by this:

"<td><input type='button' value='Update' class='btn-update' data-id='".$valor['ID']."' data-urlvideo='".$valor['url_video']."'>"."</td>"

And in JS get both parameters using .data :

  var id = $(this).data('id');
  var string = $(this).data('urlvideo');

It is also possible, as suggested by @Fernando, to check if the DB table has the correct type in the url_video field, which should be textual, such as char or varchar .

To complete, I would change this line to "bind": instead of using string concatenation (so you solve the problem of missing quotation marks, and protect yourself from injection):

Replace:

mysqli_query($con,"Update video SET url_video=$string_url WHERE ID=$id");

By:

$stmt = mysqli_prepare($con,"Update video SET url_video=? WHERE ID=?");
mysqli_stmt_bind_param($stmt, 'si', $string_url, $id);
$stmt->execute();

This will minimize the chance for SQL Injection .

SQL Injection

    
26.03.2015 / 07:02