Insert/Update MySQL into longtext Column

Question

How do I insert the following string into MySQL:

$myValue ouputs: [Hey, this is a multi text file that has special characters like this ' and this '' and this ,,"", and this ''' and this '''' and this !@$ and whatever]

But the following will not work because of special characters:

$sql = "UPDATE `mytable` SET NEWS=('$myValue') WHERE _id='1'";

I do not want to manually escape every character (like adding an ' before every ')

Update/Insert should should start at [ and end at ] (as seen in $myValue)

EDIT (mysqli)

    $_myValue = mysqli_real_escape_string($myValue);

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $sql = "UPDATE `mytable` SET NEWS='$_myValue' WHERE _id='1'";


        if ($conn->query($sql) === TRUE) {
            echo "Record updated successfully";
        } else {
            echo "Error updating record: " . $conn->error;
        }

what language are you using to generate the output/push to db? — atoms, Dec 10 '15 at 16:54

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

From the syntax of your code I assume that php is used to submit the queries to mysql.

If you just want to escape special characters in a string variable passed to a field, then use

PDO::quote() (if you use PDO)
mysqli_real_escape_string() (if you use mysqli)
mysql_real_escape_string() (if you use mysql, although you should not)

If you are looking for a more generic solution gainst sql injection, then consider using prepared statements. See this landmark SO topic on how to prevent SQL injection in php-mysql environment.

edited May 23 '17 at 12:23

Community

1
1

answered Dec 10 '15 at 17:04

Shadow

33,525
10
51
64

I edited my question with modification, I'm using mysqli however the escape_string $_myValue is returning '' (nothing) while $myValue is returning data before conversion. – user3560827 Dec 10 '15 at 17:55
ok, i included the connection,escapestring.. let me see how that works out, ill provide feedback – user3560827 Dec 10 '15 at 18:08

score 0 · Answer 2 · answered Dec 10 '15 at 16:59

If your using php you could look at using PDO;

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

$sql = "UPDATE `mytable` SET NEWS=:myValue WHERE _id=1";
$st = $conn->prepare( $sql );
$st->bindValue(":myValue", $myValue, PDO::PARAM_STR);
$st->execute();

This will input all the data stored in $myValue. I would look at sanatising the input too.

Insert/Update MySQL into longtext Column

2 Answers2