0

I have made a Wordpress plugin that writes some data to a proprietary table.

The query as shown in the log is:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)

If I take this line and enter it manually via adminer in the database, replacing the first %s with 'test' and the second %s with 1:

INSERT INTO berichten_devices (device_UUID, article_id) VALUES ('test', 1)

The this is stored in the database without a problem. There is also another table that is written to from the plugin which works fine.

The php that is responsible for storing the data is as follows:

function insert_artice_read($uuid, $article_id) {
error_log('$uuid:'.$uuid, 0);
error_log('$article_id:'.$article_id, 0);

    $query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)";
error_log('$query', 0);
error_log($query, 0);
    $query = $this->wpdb->prepare($uuid, $article_id);
    return $this->wpdb->query($query);
  }

The parameter $this->wpdb is defined as 

$this->wpdb = $wpdb;

The logging that is shown is this:

dcr-wordpress | [Tue Jun 25 10:45:03.363771 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $uuid:ABCDEF01-2345-6789-ABCD-9876543210AA
dcr-wordpress | [Tue Jun 25 10:45:03.363776 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $article_id:1
dcr-wordpress | [Tue Jun 25 10:45:03.363781 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] $query
dcr-wordpress | [Tue Jun 25 10:45:03.363785 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)
dcr-wordpress | [Tue Jun 25 10:45:03.364844 2019] [php7:notice] [pid 1953] [client 192.168.224.1:43584] 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 'ABCDEF01-2345-6789-ABCD-9876543210AA' at line 1 bij query ABCDEF01-2345-6789-ABCD-9876543210AA gemaakt door require('wp-blog-header.php'), wp, WP->main, WP->parse_request, do_action_ref_array('parse_request'), WP_Hook->do_action, WP_Hook->apply_filters, rest_api_loaded, WP_REST_Server->serve_request, WP_REST_Server->dispatch, Prop\\Inc\\Core\\RestController->article_retrieved, Prop\\Inc\\Common\\Repository->insert_artice_read
dcr-wordpress | 192.168.224.1 - - [25/Jun/2019:10:45:03 +0000] "POST /wp/wp-json/prop/v1/berichten-devices HTTP/1.1" 200 802 "-" "Mozilla/5.0 (iPhone; CPU iPhone OS 12_3_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148"

From this logging it is obvious that there is something wrong with the sql syntax, but this line seems weird to me:

for the right syntax to use near 'ABCDEF01-2345-6789-ABCD-9876543210AA'

This is data from the prepared statement parameters, I would expect to see something like

for the right syntax to use near %s

I tried to use %i instead of %s for the second parameter, just be sure, but there was no difference.

This is the table definition:

CREATE TABLE IF NOT EXISTS berichten_devices (
  device_UUID     VARCHAR(255) NOT NULL,
  article_id      int(10)      NOT NULL,
  date            timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4),

  KEY `dev_uuid` (device_UUID)

My question is basically: "What am I doing wrong?"

Improve this question
4
  • Try: `$query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (?, ?)" The ? is a parameter marker in SQL, %s is (I guess) a string variable in php
    – Lennart - Slava Ukraini
    Jun 25, 2019 at 11:25
  • this question should be on stackoverflow as there is PHP code involved, this question is simply offtopic here..
    – Raymond Nijland
    Jun 25, 2019 at 12:01
  • I tried "(?, ?)" instead of "(%s, %s)" but the result is the same.
    – Tjeerd
    Jun 25, 2019 at 12:52
  • I find it odd that you first assign query a string, and then assign it the result from prepare, but then I dont know php. This is likely more of an php question than an sql question, so I agree with Raymond that you should ask it on stackoverflow.com instead
    – Lennart - Slava Ukraini
    Jun 25, 2019 at 17:28

2 Answers 2

Reset to default
0

You likely will need to enclose the parameters in quotation marks if they are strings. Try this:

$query = "INSERT INTO berichten_devices (device_UUID, article_id) VALUES (\'%s\', \'%s\')";
Improve this answer
2
  • Thanx for your answer but it does not solve the problem, now the whole Wordpress (my local test) is down.
    – Tjeerd
    Jun 26, 2019 at 8:44
  • When you say it's down, what happened exactly? What error(s) are you getting?
    – raphael75
    Jun 26, 2019 at 14:15
0

I have changed the insert_artice_read function to:

  function insert_artice_read($uuid, $article_id) {
error_log('$uuid:'.$uuid, 0);
error_log('$article_id:'.$article_id, 0);

    $this->wpdb->query($this->wpdb->prepare("INSERT INTO berichten_devices (device_UUID, article_id) VALUES (%s, %s)", $uuid, $article_id));

  }

I am not sure why it works now but it does work. If someone knows why this works and the one question does not, please tell me because to me they seem basically the same.

Improve this answer
1
  • just a guess, but here you are replacing %s with $uuid so you basically end up with a constant instead of a parameter marker. I.e. you are likely to compile every instance of the query and not benefit from a prepared statement
    – Lennart - Slava Ukraini
    Jun 26, 2019 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.