MySQL – How to Enter Exact Values into a Table Using SQL

MySQLPHP

I am needing to insert particular values into a table I am working on with SQL (see function below):

INSERT INTO *Table Name* (...) VALUES(...);

The values that are going to be stored inside this table are values that involve another programming language that have a lot of weird symbols involving a single backslash, *, dollar signs, etc.

How can I insert values precisely as they are inside this table?

For instance, when I type '\2' it needs to return \2 inside the table instead of just 2. I am unable to manually enter the two backslashes to get \2. This is because I am receiving input from another user via HTML and sending it to my table with PHP. The issue I am having in that there are these special characters with SQL which are being entered is not the way I intend them to as values.

Is there a way to type these values in exactly with a certain string function that is called beforehand? Other ways of doing this would be greatly appreciated!

Additional Info for Database:

Server: Localhost via UNIX socket

Server type: MySQL

Server connection: SSL is not being used Documentation

Server version: 5.6.49-cll-lve – MySQL Community Server (GPL)

Potential Answer:
Is $mysqli defined to be as follows? (I call it $db.)
$mysqli=mysqli_connect(...);

So, that literals are added as follows:

$query = "INSERT INTO Table (C1, ... Cn) VALUES('$C1', ... '$Cn')"; 
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $C1, ..., $Cn);
$stmt->execute();
mysqli_query($db, $query);

Best Answer

Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.

Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".

Using parameterized queries will also ensure that the data is automatically escaped in the necessary way. So this also solves your problem. No manual escaping is necessary.

Related Solutions

Mysql – Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts

According to the MySQL Documentation

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Inserts from many clients are bundled together and written in one block (This consumes memory). This is much faster than performing many separate inserts.
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows (This consumes memory).
For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. This is definitely the case for a MyISAM table that experiences no DELETEs and UPDATEs.

Based on these excerpts from the MySQL Documentation, I would like to suggest the following options:

OPTION 1

Change your target table from MyISAM to InnoDB. This will eliminate the need to use INSERT DELAYED altogether. With InnoDB using MVCC for ACID compliance, the InnoDB log files will better protect data in the events of a crash via MySQL calling for InnoDB to do crash recovery. You will need to tune InnoDB accordingly.

OPTION 2

If you cannot switch the table to InnoDB, you must go with extended INSERTs. However, you must adjust bulk_insert_buffer_size (its default is 8M) to a much larger to compensate using extended INSERT.

Either way, you need to stop using INSERT DELAYED like yesterday.

Mysql – Help the database isn’t performing fast enough! 100M Merge with 6M need < 1 hour!

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.

Related Question