0

Hi, I'm trying to type in a name of a song into an input field, for example:
I'll Be Missing you

This field is captured through $_POST and set to a variable $title

I then update the table with this new title. Once it is updated, all that is shown in the data is:

I

The single quote, and anything after it is gone completely.
Here is my query. How can I change this so it includes the single quote and everything after it?

$sql = "UPDATE sheets SET artist = '$artist', title = '$title', active = '$activestatus' WHERE id = $value";
        $result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);

If more code is required to understand what I'm talking about, let me know.

3
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by Lsmjudoka
0
$sql = sprintf("UPDATE sheets SET artist = '%s', title = '%s', active = '%s' WHERE id=%s "
				,mysql_real_escape_string($artist)
				,mysql_real_escape_string($title)
				,mysql_real_escape_string($activestatus)
				,mysql_real_escape_string($value)
				);
$result = mysql_query($sql) or die(mysql_error().'<br>'.$sql);
0

Let me explain what's going on:

SQL parses by single quotes. So it takes whatever is inside a pair of single quotes as the item. E.g:

$sql = "SELECT * FROM Books WHERE name='$book_name'";

If $book_name is "Over the rainbow", the query sent to the server is this:

SELECT * FROM Books WHERE name='Over the rainbow'

That's fine. But if the name is "Earil's mysteries":

SELECT * FROM Books WHERE name='Earil's mysteries'

The query gets stopped. Use mysql_real_escape_string() to add slashes to escape the characters before it gets sent to the server. Given:

$book_name = mysql_real_escape_string($_POST['book_name']);
$sql = "SELECT * FROM Books WHERE name='$book_name'";
mysql_query($sql);

The server will get this query:

SELECT * FROM Books WHERE name='Earil\'s mysteries'

The \ tells MySQL to ignore the ' and it works. Side note: This is VERY important to do with all user input, as hackers/script kiddies can use SQL injection to gain access to your DB otherwise.

Edited by Lsmjudoka: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.