0

I have a query that I am trying to update a MySQL table with

UPDATE `" . TABLE_PREFIX . "TABLE` SET FIELD = CONCAT(FIELD, " . $MyString . ")

$MyString would contain a useragent like MOZILLA/5.0 (COMPATIBLE; SEMRUSHBOT/1.2~BL; +HTTP://WWW.SEMRUSH.COM/BOT.HTML but I am getting a syntax error? The field is set up as TEXT NULL and default is NULL.

Any help appreciated.

For clarity all I am trying to do is add text to a text field in the database, the text IS exactly like the useragent above!

Kind regards, Simon

Bananaapple
  • 2,984
  • 2
  • 25
  • 38
Silo
  • 3
  • 3
  • 1
    What is the error message? What is the name of the table you are trying to update? – Lennart - Slava Ukraini Aug 22 '17 at 21:20
  • Could you update your question since it is not entirely clear what your trying to archieve. – Noob Aug 22 '17 at 21:24
  • Ok where I have put TABLE in my question the actual line would read uAgent, here's the error UPDATE `ST_uAgent` SET agent = CONCAT(agent, MOZILLA/5.0 (COMPATIBLE; DOTBOT/1.1; HTTP://WWW.OPENSITEEXPLORER.ORG/DOTBOT, HELP@MOZ.COM)) – Silo Aug 22 '17 at 21:25
  • could you use prepared statement? this one for preventing sql injection attack and your **character escaping** issue. – Bagus Tesa Aug 22 '17 at 21:37

3 Answers3

2

String delimiters in sql are ', I assume that your query in the question is some kind of mix between php and sql. I would suggest that you start by getting the sql correct first, and after that incorporate it into php. Your query probably should look something like:

UPDATE ST_uAgent 
    SET agent = CONCAT(agent, 'MOZILLA/5.0 (COMPATIBLE; DOTBOT/1.1; HTTP://WWW.OPENSITEEXPLORER.ORG/DOTBOT, HELP@MOZ.COM)');
Lennart - Slava Ukraini
  • 6,936
  • 1
  • 20
  • 32
0

You need to put ' quotes ' around your string.

"UPDATE `" . TABLE_PREFIX . "TABLE` SET FIELD = CONCAT(FIELD, '". $MyString ."')"

Without this MySQL doesn't know how to interpret the data you are passing it.

That said, what you are attempting here is extremely risky and leaves you wide open to SQL injection.

Consider using parameterisation / prepared statements as provided by mysqli or PDO instead - see here: How can I prevent SQL injection in PHP?

Bananaapple
  • 2,984
  • 2
  • 25
  • 38
  • 1
    That's great and worked however there isn't any spaces or carriage returns so everything is reading as one long string. I will take a look at the PDO thanks for pointing that out. – Silo Aug 22 '17 at 21:49
  • 1
    I Agree, just make sure to use double quotes because that's what you're using to concat. – Samuel Ramzan Aug 22 '17 at 22:17
0

I guess the problem is the delimiter ´. Eliminate the delimiter:

("UPDATE ". TABLE_PREFIX ." TABLE SET FIELD = CONCAT(FIELD, '". $MyString ."')");

It's just a guess.

Samuel Ramzan
  • 1,770
  • 1
  • 15
  • 24