Inserting records into MySQL DB

Question

Im using the follow script to insert records into my DB:

$sql = "INSERT INTO fotetweets VALUES('$tweetid','$dp', '', '$username','$tag', '$twittercontent', '$twittertimestamp', '')";
mysql_query($sql);

However what if $twittercontent contains the ' char, I think it will fail. Correct?

If so how can I deal with it correctly?

Answer 1

That's why you should use mysql_real_escape_string() funtion first

http://www.php.net/manual/en/function.mysql-real-escape-string.php

It is important, that you always escape (or in general sanitize) variables you interpolate into your queries that come from untrusted sources (i.e. not from you ;) ). Topic for you to Google for read about: 'SQL injection'

Answer 2

Correct. Not only it will fail but it will also leave you open to SQL Injection attacks.

To avoid these problems, you can use:

• mysql_real_escape_string()
• PDO and Prepared Statements

Remember, user input should always be sanitized.

Answer 3

You will want to look into mysql_real_escape_string. However, I would look into using the mysqli or PDO class instead and utilize prepared statements.

EDIT

Note, these can all be found / were pretty much taken from the PHP Manual under examples for prepared statements.

Example Usage for MySQLi:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "my_database");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* create a prepared statement */
$stmt =  $mysqli->stmt_init();
if ($stmt->prepare("INSERT INTO fotetweets VALUES(?, ?, '', ?, ?, ?, ?, '')")) {
    /* bind parameters for markers */
    $stmt->bind_param("issssi", $tweetid, $dp, $username, $tag, $twittercontent, $twittertimestamp);

    /* execute query */
    $stmt->execute();

    /* close statement */
    $stmt->close();
}
?>

Example Usage PDO:

<?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sth = $dbh->prepare('INSERT INTO fotetweets VALUES(?, ?, '', ?, ?, ?, ?, '')');
$sth->execute(array($tweetid, $dp, $username, $tag, $twittercontent, $twittertimestamp));
?>

Example of mysql_real_escape_string usage:

$tweetid = (int) $tweetid; // static cast to integer, if that is what it should be.
$sql = "INSERT INTO fotetweets VALUES(
    $tweetid,'" . mysql_real_escape_string($dp) . "', 
    '', '" . mysql_real_escape_string($username) . "', 
    '" . mysql_real_escape_string($tag) . "', 
    '" . mysql_real_escape_string($twittercontent) . "', 
    '" . mysql_real_escape_string($twittertimestamp) . "', '')";

You can find more information and extra usage examples at the manual pages listed above. Given I do know what $dp is I cannot tailor this exactly.

SIDE NOTE

This is all the assumption I am willing to make. The OP could be getting the data from POST and/or in an array form, or he could be getting it from a different means. Either or, given the example the OP posted, this is as accurate as I could be to tailor to the OP. If you have an issue or think it could be better explained / shown, well go ahead and add another answer which addresses it and not just another complaining comment remark about how no one does anything right when you are not willing to pony up the "correct" answer yourself.

And of course if it is an array, this changes a lot of items and the OP should clear that up and not just random people making "guesses" as to where and how the data is being retrieved.

Answer 4

Just before you run this query, use this:

$twittercontent = mysql_real_escape_string($twittercontent);

Answer 5

yes it would fail as it would prematurely terminate the string. To fix this use

mysql_real_escape_string($twittercontent) in place of $twittercontent

Answer 6

As it was mentioned above, you have to use mysql_real_escape_string()

note that you have to use this function not for the $twittercontent variable only, but for the every field in the query
and not only for inserting and not only for this table.
and from "untrusted input".

But literally every variable you are going to put into query in quotes, should be processed with this function. No exceptions or conditions.
Note if you don't put variable in quotes in the query, this function become useless

Another way, already mentioned too, is to change entire database driver.
Unfortunately, noone bring a useful example of real life usage, which could be extremely useful.

Answer 7

Make your life simpler:

//$pdo = new PDO("mysql:host=localhost;dbname=testdb", user, pass);

$pdo->prepare("INSERT INTO fotetweets VALUES (?,?,?,?,?,?,?,?)")
    ->execute( array($tweetid, $dp, '', $username, $tag, $twittercontent, $twittertimestamp, '') );

This sends the data correctly to the database, without security issues. Use it as template for all queries. (Note that you still have to apply htmlspecialchars() when outputting your database content again later...)

Answer 8

Hi,

As it was mentioned before me you can use mysql_real_escape_string
OR
if you use PDO you can also use binding and the you do not have to worry about escaping.

$stmt = $db->prepare("INSERT INTO fotetweets VALUES(:tweetid,:dp, '', :username,:tag, :twittercontent, :twittertimestamp, '')");
$stmt->bindParam(':tweetid', $tweetid);
$stmt->bindParam(':dp', $dp);
$stmt->bindParam(':username', $username);
$stmt->bindParam(':tag', $tag);
$stmt->bindParam(':twittercontent', $twittercontent);
$stmt->bindParam(':twittertimestamp', $twittertimestamp);
$stmt->execute();

Answer 9

You can also use addslashes(). mysql_real_escape_string() is better though. I agree with using PDO.