Insert a text mixed with double and single quotes into the sql database-CodePudding

I have a form and I let users type some additional details for the form. What if the user enters a text mixed with single and double quotes.

ex:- I'd like to join. So we"are party"this"time'addtional'details`willbe"

I found out that, because of this text, nothing goes into the database. It means literally this thing crashes my whole program.

How do I enter it into the database using PHP or MySQL query?

I've tried these methods

Backslashes (')
JSON encode
str_repalce
htmlspecialchars with ENT_QUOTES

But nothing works for me!

CodePudding user response：

Just use prepared statements. https://www.php.net/manual/en/pdo.prepare.php this should help with all your escape problems, also offers protection for sql injection

CodePudding user response：

ATTENTION! You should really be using parameterized queries along with prepared statements, using either MySQLi or PDO! Otherwise, you're opening yourself up to SQL Injection (SQLI) attacks! Another good reason, is that you don't have to worry about escaping your input(s) anymore. The MySQLi/PDO implementation will handle this for you.

I will move forward with MySQLi in my example. Whichever you end up choosing is completely up to what you prefer at the end of the day.

<?php
// Error handling
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

/*!
 * MySQLI Server Connection
 * The DBHOST, DBUSEr, DBPASS, DBNAME,
 * are define variables. Make your own.
!*/

$conn = new mysqli(DBHOST, DBUSER, DBPASS, DBNAME);

// Language of the server
mysqli_query($conn, "SET character_set_results=utf8");
mb_language('uni'); 
mb_internal_encoding('UTF-8');
mysqli_query($conn, "set names 'utf8'");

// Function to check if connection is alive
function checkConnection($conn) {
    if ($conn->ping()) {
        printf ("Our connection is ok!\n");
    } else {
        printf ("Error: %s\n", $conn->error);
    }
}

$conn->autocommit(FALSE);

/*!
 * table is your table name,
 * column is the column name.
!*/

$sql = "INSERT INTO table SET column = ?";
$stmt = $conn->prepare($sql);

/*!
 * the 's' argument here indicates that
 * the insert variable is a string type.
 * There are of course multiple types,
 * and you can look them up.
 * You chain them like so:
 * $stmt->bind_param('iss', $value1, $value2, $value3);
 * In the example above, we are expecting int, string, string.
 * so $value1 is an int, $value2 is a string, $value3 is another string.
 * The params are handled chronologically!
 * Back to the actual example again:
 * $value is is your post variable
 * (i.e. the string you want to insert).
!*/

// bind the param(s), there's only one in this case.
$stmt->bind_param('s', $value);
// execute the query.
$stmt->execute();
// commit, we had set the auto commit to be false earlier.
$conn->commit();
// closing the connection again.
$stmt->close();
$conn->close();
?>

Ideally, you'll split the database and connection setup into another file called maybe dbconnect.php and have the SQL logic be in a function, or handled in some other, separate logic, using include to include the datbase connection logic.