1

I want to write an image received via http into my database using PHP. I access the image with 

$inputImage = file_get_contents('php://input');

Echoing the $inputImage works fine, so the transport to the server doesn't seam to be a problem.

I now tried to insert the image with

$sqlRequest="INSERT INTO Image(time, data) SET (NOW(), '$inputImage')";
mysqli_query($connection, $sqlRequest) or die("Error in Inserting " . mysqli_error($connection));

But it doesn't work and i recieve the following error

Error in Inserting You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET (NOW(), '����' at line 1

Can someone give me a hint thanks

edit: okay changed the sytax problem, got to look for the blob problem

Donald
  • 13
  • 3
  • 1
    that isn't how it works. You're instructing MySQL to set a column named `NOW()` as in a "function". RTM http://dev.mysql.com/doc/en/insert.html and using improper syntax, plus you've an encoding problem. You're trying to upload as a BLOB but not doing that. You need to escape it. – Funk Forty Niner Jan 08 '16 at 14:36
  • okay I got the wrong syntax, changed it to $sqlRequest="INSERT INTO Image (data) Values ($inputImage)"; Still the same problem how to solve the encoding problem? – Donald Jan 08 '16 at 15:08
  • is your column set a BLOG or LONGBLOB? plus, you need to escape that file's variable; sounds like a solution to me. Give me a minute, I will write something up for you. – Funk Forty Niner Jan 08 '16 at 15:17
  • you're welcome. See my answer I posted for you below. – Funk Forty Niner Jan 08 '16 at 15:21

3 Answers3

1

Use VALUES() instead of SET(). SET is meant for updating (using UPDATE) whereas VALUES() is meant for inserting (using INSERT).

See this for INSERT syntax and this for UPDATE syntax.

pietv8x
  • 248
  • 1
  • 9
0

you want to make sure no data obstructing your sql string is stored so do this:

$inputImage = base64_encode(file_get_contents('php://input'));

before storing the data and then

$myImage = base64_decode( myGetImageFromDBFunction( $myImageID )  );
Max
  • 2,561
  • 1
  • 24
  • 29
0

Looking at the funny characters ����, this sounds like you're trying to upload a binary file into a column that isn't capable of handling that type.

Sidenote edit: seeing your comment now, you still need to escape that variable.

By "escape", I mean that you need to use mysqli_real_escape_string().

If this isn't set, then you will need to ALTER your column to either be a BLOB or LONGBLOB.

More importantly, you need to escape the contents of $inputImage because that will be binary data and could contain bytes that will cause MYSQL to assume it is shorter than it actually is.

$inputImage = mysqli_real_escape_string($connection, $inputImage);

$sqlRequest="INSERT INTO Image(time, data) VALUES (NOW(), '$inputImage')";

And of course as I already stated in comments under your question, you're using the wrong syntax in your query.

Use VALUES() and not SET().

Add or die(mysqli_error($connection)) to mysqli_query() also.

Reference:

Using a prepared statement would also work:

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
  • thank your very much, works perfectly now but to be honest I didn't understand what it means to escape the variable and what the function does – Donald Jan 08 '16 at 15:28