Entering Exact Values into a Table Using SQL

Question

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);

Answer 1

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.

Answer 2

You are looking for a way to enter string constants.

---

Say we want to enter “\X*$Y$!'Z,"”
; then,

• per the SQL standard, '\\X*$Y$!''Z,"'

  • we \-escaped the \
  • we '-escaped the '
  • see http://brogoff.com/vertica/HTML/SQLRef/8953.htm
  • this should work wherever SQL standard is supported.

• Oracle-specific: depends on column datatype; see https://stackoverflow.com/a/1192431/13950739

• SQL Server-specific: need N prefix; see https://stackoverflow.com/a/31270557/13950739 and https://stackoverflow.com/a/6380696/13950739

• with PostgreSQL's “C-style escape” syntax, E'\\X*$Y$!\'Z,"'

  • note the prefix E, so beginning delimiter is E'; ending delimiter is just '.
  • we \-escaped the \
  • we also \-escaped the '
  • see https://www.postgresql.org/docs/current/sql-syntax-lexical.html , under String Constants With C-Style Escapes

• with PostgreSQL's “Unicode escape” syntax, U&'\005CX*$Y$!\0027Z,"'

  • note the prefix U&, so beginning delimiter is U&'; ending delimiter is just '.
  • 5C is ASCII code (hence also UTF8 code) of \, in hex
  • 27 is ASCII code (hence also UTF8 code) of ', in hex
  • see https://www.postgresql.org/docs/current/sql-syntax-lexical.html , under String Constants With Unicode Escapes

• with PostgreSQL's “Dollar-quoting” syntax, $flarp$\X*$Y$!'Z,"$flarp$

  • beginning delimiter is $ plus an optional sequence of arbitrary characters (I used flarp and you can use whatever you like) plus $; ending delimiter is the same thing.
  • this is the only approach that lets you enter the original string unchanged --- provided it does not contain $whateverSpecialDelimiterTagYouChose$ ,that is!
  • see https://www.postgresql.org/docs/current/sql-syntax-lexical.html , under Dollar-Quoted String Constants

Answer 3

I believe you just need to insert a backslash behind every special character to escape it. The backslash is reserved as a special escape character in MySQL (and even escapes other backslashes) so that the proceeding character is treated as a literal. You can read more about it in the MySQL Docs.

If you're using PHP, depending on what version, you can also leverage one of these functions on the application side to escape the special characters.