0

So I'm using a little script on php for a webpage adminisitration, and I get to do an item registration, so I get all my params and send them to the script to build an INSERT for the database (mysql). Here's my piece of code:

//Getting the params
    $title = $_POST["title"];
    $date = $_POST["date"];
    $hour = $_POST["hour"];
    $description = $_POST["description"];
    $link = $_POST["link"]; 
    $speaker = $_POST["speaker"];
    $site = $_POST["site"];
    $file = $_POST["file"];
//Link and File are optional, so I'll be using NULL instead if they're empty
    $link = !empty($link) ? ("'".$link."'") : ("'". NULL ."'");
    $file = !empty($file) ? ("'".$file."'") : ("'". NULL ."'");
//Now I'm ready to build the query
    $query = "INSERT INTO ".$type;
    $query = $query . "(title,data,hour,description,link,speaker,site,file)";
    $query = $query . "VALUES (";
    $query = $query . "'" .$title."'";
    $query = $query . ",'".$date."'";
    $query = $query . ",'".$hour."'";
    $query = $query . ",'".$description."'";
    $query = $query . ",".$link;
    $query = $query . ",'".$speaker."'";
    $query = $query . ",'".$site."'";
    $query = $query . ",".$file.")";
//Finally, I'll be sending the INSERT as a query using:
    $result = mysql_query($query);
    if(!$result)
        echo "SQL Error"

And so, I'm always getting inside the error statment. I've others INSERTS in other scripts on the same webpage, and they work well, this one mimics them. I've checked:

  1. mysql_connect() and mysql_select_db() are ok
  2. Database user I use has GRANTS to do the INSERT
  3. Database connectivity (checked using a SELECT query)

Any hint will be appreciated.

[SOLVED] Strings were not escaped, so the quotes were breaking the query. So if you're still issuing this kind of trouble and using the deprecated mysql _ API, you may as well need for the mysql_escape_string method (check Escaping single quote in PHP when inserting into MySQL ).

Community
  • 1
  • 1
ytturi
  • 1
  • 1
  • enter your table structure as well as the coding of form – Passionate Coder May 31 '16 at 07:31
  • 2
    **Stop** using deprecated `mysql_` API. use `mysqli_` or `PDO` instead with prepared statement – Jens May 31 '16 at 07:32
  • 1
    Call the mysql_error() function to find out what the error is. – Jens May 31 '16 at 07:32
  • 1
    Where is `$type` variable is coming from. Also your code is open for sql injections – Ahmed Khan May 31 '16 at 08:21
  • @ytturi, have you checked my answer below and tried it. – Nehal May 31 '16 at 08:55
  • Thank you all for answering. @AhmedKhan the $type is used before the query is build. I've checked it testing in the final string, it's a correct value. – ytturi Jun 02 '16 at 09:51
  • @mamta the table structure is basically as you may think. All are text values, but the date being a date and the hour being a time type value. – ytturi Jun 02 '16 at 09:53
  • @Jens sorry for using deprecated API, but it's not my choice right here > – ytturi Jun 02 '16 at 09:55
  • OK, so this script was working properly, so the problem was my mate that assured me that the strings were correctly escaped (and they didn't...) >< thank you all for your time anyway ^^ – ytturi Jun 02 '16 at 11:12

2 Answers2

0

use these lines.

$link = !empty($link) ? $link : NULL;
$file = !empty($file) ? $file  :NULL ;

in PHP null is NULL without quotes also don't use extra "" in $link and $file.

before running query try to print it and run in PhpMyadmin

Passionate Coder
  • 7,154
  • 2
  • 19
  • 44
  • Hey ,thank you for answering. As answered to @Ms.Nehal, I missed on transcripting from my original code, I'm using no quotes for the NULL value, but I'm adding the quotes for the concatenation string. Sorry about that, I'm editing it now. – ytturi Jun 02 '16 at 09:40
0

You have issues with concatenation. Try to change your code lines with this :

 //Link and File are optional, so I'll be using NULL instead if they're empty
  $link = !empty($link) ? ($link) : (NULL);
  $file = !empty($file) ? ($file) : (NULL);
//Now I'm ready to build the query
  $query = "INSERT INTO $type (title,data,hour,description,link,speaker,site,file) VALUES ('" .$title."','".$date."'
           ,'".$hour."','".$description."','".$link."','".$speaker."','".$site."','".$file."')";

Also, NULL is never passed as a string. Otherwise, it'll be treated as a string instead.

Nehal
  • 1,542
  • 4
  • 17
  • 30
  • Yup, thank you about that. I saw that same mistake in other instances in this page. I think i did some transcription errors when erasing sensitive data from the comments, my line for empty link/files are: $link = !empty($link) ? ("'".$link."'") : ("'". NULL ."'"); $file = !empty($file) ? ("'".$file."'") : ("'". NULL ."'"); So in the query sent, if you print the string, the result looks as '' when the value is NULL. – ytturi Jun 02 '16 at 09:36
  • @ytturi, I'm sorry what you're trying to say is not clear. Please can you specify more breifly – Nehal Jun 02 '16 at 09:52
  • I did an error when I wrote the post. The concatenation is working well and the NULL value is sent properly. – ytturi Jun 02 '16 at 10:59
  • @ytturi, so you have solved your issue. So better you should post your and accept it as well, which shows the solution to the problem – Nehal Jun 02 '16 at 11:53