How do I prepare strings for insertions as values into a MySQL table?

Question

I am trying to insert some strings containing hyphens and apostrophes as values into a MySQL table. I assign the string value:

$venue_name = addslashes(get_the_title($id));

When I echo the value, I get:

4B’s Brewery

and

Ace Hardware - Bailey

My SQL statements looks like this:

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name,
 website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description,
 doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES
 (817, 7, 'Active', '4B’s Brewery', 'http://4bsbrewery.com/', '215 W Main St',
 '', 'Cedaredge', 'Colorado', '81413', '970-856-7762', '', '', 0, 0, '', '', '',
 0, '168', 89, 0)

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name,
 website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(915, 7, 'Active', 'Ace Hardware – Bailey', '', 'Moore Lumber & Hardware', '',
 'Bailey', 'Colorado', '', '720-924-3999', '', '', 0, 0, '', '', '', 0, '168',
 89, 0)

I am using

dbDelta($sql);

to update the database.

But I am getting database errors like this:

    WordPress database error: [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 ''4B&#8217' at line 4]

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name, 
website, address_1, address_2, city, state, postal_code, phone, fax, email,
 logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(817, 7, 'Active', '4B&#8217



    WordPress database error: [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 ''Ace Hardware &#8211' at line 4]

    INSERT INTO wp_w2bw2c_venue (id, market_id, venue_status, venue_name, 
website, address_1, address_2, city, state, postal_code, phone, fax, email, 
logo_attachment_id, featured_image_attachment_id, tagline, description, 
doors_open, capacity, venue_type_id, salesperson_id, landing_page_id) VALUES 
(915, 7, 'Active', 'Ace Hardware &#8211

The special characters ' and - are terminating the strings. I have tried addslashes(), str_replace('-', '-", $venue_name), and mysql_real_escape_string(), with no success. How do I prepare the strings for acceptance as MySQL VARCHAR table column values?

Answer 1

dbDelta is really meant for creating databases or tables, not inserting records.

I'd suggest using $wpdb->insert(), which will escape the values for you:

$wpdb->insert( 
    $wpdb->prefix . 'w2bw2c_venue', 
    array(
        'market_id'  => 7,
        'venue_name' => get_the_title( $id ),
    ),
    array(
        '%d',
        '%s',
    )
);

I've just used the name field you were having trouble with in my example, obviously you'll want to fill out the rest. I included market_id to show the correct usage of the second $format array, ensuring that market_id is parsed as an integer.