GET parameter for INT does not want to update

Question

<?php
 header('Access-Control-Allow-Origin: *');
 header("Access-Control-Allow-Headers : Content-Type");
 include_once("db_connect.php");
  if(isset($_GET["u"])){
   $username = $_GET['u'];
    } else {
    echo "No UserName";
    exit();
   }
 if(isset($_GET["v"])){
  $video= $_GET['v'];
  } else {
   echo "No Video ID";
  exit();
 }
  if(isset($_GET["like"])){
   $like = $_GET["like"];
    } else {
    echo "No Like Parameter added.";
   }
 $sql = "SELECT * FROM rating WHERE video='$video' LIMIT 1";
 $video_query = mysqli_query($db_connection, $sql);
 $numrows = mysqli_num_rows($video_query);
 if($numrows < 1){
 $sql = "INSERT INTO rating (video,username)
         VALUES ('$video','$username')";
 $video_query = mysqli_query($db_connection, $sql);
  } 

  if(isset($_GET['like'])){
   $counter = (int)$_GET["like"];
 if($counter > 5 || $counter < 1){
   echo "Rating Seems To Be Off?";
   exit();
   }
 $sql = "UPDATE rating SET like='$counter' WHERE video='$video' AND username='$username'";
 $video_query = mysqli_query($db_connection, $sql);
  echo "Voted";
 }else {
   echo "No Parameter to Vote was applied";
 exit();
 }
?>

Basically how I am writing the GETs are

?u=USERNAME&v=video0009&like=4

I want the like=4 to then update the INT from where the Video and Username match. Though it keeps staying at 0.

Also Keeping these as INT will that make it so that later I can count these together with mysqli_fetch_assoc ? just curious

vee · Accepted Answer · 2013-08-25T06:54:28.127

2

like is a mysql reserved word so you need to quote them using back ticks.

Replace your query:

$sql = "UPDATE rating SET like='$counter' WHERE video='$video' AND username='$username'";

with:

$sql = "UPDATE rating SET `like`='$counter' WHERE video='$video' AND username='$username'";

There are several other recommendations for your code. First and probably the most important is SQL Injection. Please read on SQL Injection here. Have a look on how you can implement mysqli_real_escape_string. You are passing raw input directly into your database.

edited Aug 25 '13 at 06:54

answered Aug 25 '13 at 06:48

vee

38,255
7
74
78

Oh no wonder why. ok so I will change this and see if it works. IF so thank you! – EasyBB Aug 25 '13 at 06:49
Since I am new to this all, I tried reading the SQL injection and not sure what you mean. – EasyBB Aug 25 '13 at 06:51
1

@EasyBB, on SQL Injection, have a read on https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet, as it describes the prevention options. – vee Aug 25 '13 at 06:53
Oh so basically he is saying using `preg_replace(#[a-z0-9]#i,$_GET['u']);` to sanitize it and prevent injections? I'm not sure really if I would need mysqli_real_escape_string or is that better than preg_replace? – EasyBB Aug 25 '13 at 06:55
Stop using regular sql statements and start using prepared statements. See http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/ & http://php.net/manual/en/mysqlinfo.api.choosing.php – Nick Aug 25 '13 at 06:56
You could do `preg_replace` if you want to filter inputs. A better solution would be to use prepared statements with parameterized queries using PDO (http://php.net/manual/en/book.pdo.php). – vee Aug 25 '13 at 06:57
@Nick New Here buddy ;) Just started like 5 days ago writing PHP lol. @Vinodadhikary thanks I'll look into PDO. I was just wondering if I could use the `preg_replace` instead of `mysqli_real_escape_string` or what is the difference really. I want to soak up all the knowledge I can ;) – EasyBB Aug 25 '13 at 06:59
If you just started, you'd better do it right the first time :P Have fun! – Nick Aug 25 '13 at 07:01
@Nick Reading more into your NETTUTS link I see it's PDO, And they write like `=>` which I've never done sadly. I've only used basic mysqli – EasyBB Aug 25 '13 at 07:02

score 0 · Answer 2 · edited May 23 '17 at 12:03

1 like is mySql reserved keyword

2 your code is vulnerable to sql injection

$video= $_GET['v'];
$sql = "SELECT * FROM rating WHERE video='$video' LIMIT 1";

Warning : you are using Mysqli it doesn't mean you eliminated risk of Sql Injection your code is still vulnerable to sql injection you need to escape all get, post and request and the better approach will be using Prepared statement

Good Read

How to prevent SQL injection in PHP?

GET parameter for INT does not want to update

2 Answers2