2

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);
3
  • 1
    Hi, I believe the lack of response to your question is because you didn't mention what database system (Microsoft SQL Server, MySQL, PostgreSQL, MongoDB, etc) and version you're currently using and therefore the answers to your question could vary. Could you please include more information about your database system?
    – J.D.
    Jan 1, 2021 at 1:48
  • 1
    @J.D. I added some additional info to the question. It is through MySQL. Let me know if you need anything else. Sorry about that.
    – W. G.
    Jan 1, 2021 at 2:08
  • No worries, thanks for updating! Generally more information is better as long as it's directly relevant and helps describe the problem.
    – J.D.
    Jan 1, 2021 at 2:15

3 Answers 3

6

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.

1
  • You get my upvote for a 2-for-1 solution, that acknowledges best security practice. :)
    – J.D.
    Jan 1, 2021 at 15:54
3

You are looking for a way to enter string constants.


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

1
  • this should work wherever SQL standard is supported. - not always a good assumption where MySQL is concerned! :-) +1 for a comperhensive answer for PostgreSQL - even though the question was about MySQL!
    – Vérace
    Jan 1, 2021 at 17:16
2

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.

Your Answer

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

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