I am trying to fix a problem with inserting data into a mysql database. If the user inserts something with a ' in it (like Aaron's) the INSERT doesn't work because the variables are sent inside 's
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron's" then the insert looks like VALUES('Aaron's', and it doesn't know what the s' is for.
I could check for 's and change them into something else (like '
and then convert it back when the data is retrieved, but is that the best idea?Originally posted by AaronCampbell
I am trying to fix a problem with inserting data into a mysql database. If the user inserts something with a ' in it (like Aaron's) the INSERT doesn't work because the variables are sent inside 's
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron's" then the insert looks like VALUES('Aaron's', and it doesn't know what the s' is for.
I could check for 's and change them into something else (like ' and then convert it back when the data is retrieved, but is that the best idea?
always use addslashes() on values going to the db:
$goat = "potato 'meal'";
$chicken = addslashes($goat);
this will escape both types of quotes.And then use stripslashes() when displaying info from the db.Thanks...that worked perfectly...I thought it would be something simple.Double up the single quote, shouldn't that work?
Aaron's -> Aaron''sOriginally posted by agent002
Double up the single quote, shouldn't that work?
Aaron's -> Aaron''s
Nope, at least not in php, the reason Willy wants him to use strip slashes all the time is an easy hole for hackers to exploit. The hacker could potentially end your current query and cleverly craft his own into it. He'd need to know how the general database is setup, but besides that it would be easy.Originally posted by Jokerman
Nope, at least not in php, the reason Willy wants him to use strip slashes all the time is an easy hole for hackers to exploit. The hacker could potentially end your current query and cleverly craft his own into it. He'd need to know how the general database is setup, but besides that it would be easy.
Thats a new nickname for me
yeah...addslashes() is to protect against sql injection attacks.One of the ways you can help protect yourself,others are more limiting input wise such as removing all SELECT INSERT UPDATE DELETE and other SQL keywords from input..so addslashes is the common way.That's what bassrek said here (<!-- m --><a class="postlink" href="http://forum.weborum.com/index.php?showtopic=345">http://forum.weborum.com/index.php?showtopic=345</a><!-- m -->). And because it's a MySQL question, it can't depend on the language you use for the queries, PHP in this case. And besides, I even suspect the addslashes() function changes the single quotes in this way.Originally posted by agent002
That's what bassrek said here (<!-- m --><a class="postlink" href="http://forum.weborum.com/index.php?showtopic=345">http://forum.weborum.com/index.php?showtopic=345</a><!-- m -->). And because it's a MySQL question, it can't depend on the language you use for the queries, PHP in this case. And besides, I even suspect the addslashes() function changes the single quotes in this way.
Indeed you are correct about not assuming the language.But atchually....addslashes does exactly that...escapes quotes with a backslash ...ie: \' \"
My post was meant to expand on doing it in php...not to be taken as a generalization altho now re-reading it it did indeed sound like one The addslashes() changes ' to \' " to \" and \ to \\
The reasone I can't change ' to '' is because it would still read the INSERT wrong...
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron''s" then the insert looks like VALUES('Aaron''s', and it doesn't know what the 's' is for, because it thinks the value starts at the first ' and ends at the second '.
The addslashes fixes that perfectly.
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron's" then the insert looks like VALUES('Aaron's', and it doesn't know what the s' is for.
I could check for 's and change them into something else (like '
and then convert it back when the data is retrieved, but is that the best idea?Originally posted by AaronCampbell I am trying to fix a problem with inserting data into a mysql database. If the user inserts something with a ' in it (like Aaron's) the INSERT doesn't work because the variables are sent inside 's
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron's" then the insert looks like VALUES('Aaron's', and it doesn't know what the s' is for.
I could check for 's and change them into something else (like '
and then convert it back when the data is retrieved, but is that the best idea? always use addslashes() on values going to the db:
$goat = "potato 'meal'";
$chicken = addslashes($goat);
this will escape both types of quotes.And then use stripslashes() when displaying info from the db.Thanks...that worked perfectly...I thought it would be something simple.Double up the single quote, shouldn't that work?
Aaron's -> Aaron''sOriginally posted by agent002
Double up the single quote, shouldn't that work?
Aaron's -> Aaron''s
Nope, at least not in php, the reason Willy wants him to use strip slashes all the time is an easy hole for hackers to exploit. The hacker could potentially end your current query and cleverly craft his own into it. He'd need to know how the general database is setup, but besides that it would be easy.Originally posted by Jokerman
Nope, at least not in php, the reason Willy wants him to use strip slashes all the time is an easy hole for hackers to exploit. The hacker could potentially end your current query and cleverly craft his own into it. He'd need to know how the general database is setup, but besides that it would be easy.
Thats a new nickname for me
yeah...addslashes() is to protect against sql injection attacks.One of the ways you can help protect yourself,others are more limiting input wise such as removing all SELECT INSERT UPDATE DELETE and other SQL keywords from input..so addslashes is the common way.That's what bassrek said here (<!-- m --><a class="postlink" href="http://forum.weborum.com/index.php?showtopic=345">http://forum.weborum.com/index.php?showtopic=345</a><!-- m -->). And because it's a MySQL question, it can't depend on the language you use for the queries, PHP in this case. And besides, I even suspect the addslashes() function changes the single quotes in this way.Originally posted by agent002 That's what bassrek said here (<!-- m --><a class="postlink" href="http://forum.weborum.com/index.php?showtopic=345">http://forum.weborum.com/index.php?showtopic=345</a><!-- m -->). And because it's a MySQL question, it can't depend on the language you use for the queries, PHP in this case. And besides, I even suspect the addslashes() function changes the single quotes in this way.
Indeed you are correct about not assuming the language.But atchually....addslashes does exactly that...escapes quotes with a backslash ...ie: \' \"
My post was meant to expand on doing it in php...not to be taken as a generalization altho now re-reading it it did indeed sound like one The addslashes() changes ' to \' " to \" and \ to \\The reasone I can't change ' to '' is because it would still read the INSERT wrong...
$result = mysql_query("INSERT INTO $board (d_name, price_per_unit, date_time) VALUES ('$d_name', $price_per_unit, '$date_time')");
So if the $d_name holds "Aaron''s" then the insert looks like VALUES('Aaron''s', and it doesn't know what the 's' is for, because it thinks the value starts at the first ' and ends at the second '.
The addslashes fixes that perfectly.