Entering Exact Values into a Table Using SQL

Posted on

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

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);
mysqli_query($db, $query);

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.

You are looking for a way to enter string constants.

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *