0

I am trying to add some data into a MySQL database using a text area. However, when someone adds in an apostrophe it breaks the INSERT command because it acts as a single quote. How can this be fixed?

Here is what the command would look like if you stipped out all the variables that I am using.

INSERT INTO skills09 (name, birthday, skills) VALUES ('Tom Haverford', '31_02_1987', 'Being Awesome, Announcing cool things, Treatin' Yo Self, Failing');

As I was looking at this I had a thought.

Is it as simple as using double quotes around my variable names rather than single quotes? This seems like an easy fix but I have always used single quotes in MySQL.

Parzi
  • 694
  • 2
  • 10
  • 33
  • Do you use PDO or MySQLi to connect to the database? There are build in methods to escape sql parameters. Take a look here: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Roland Starke Nov 25 '17 at 07:43
  • I am using MySQLi – Parzi Nov 25 '17 at 19:06
  • Use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – Code4R7 Nov 27 '17 at 15:37
  • That is a very long document full of a lot of information think you could point me to the correct spot within that document? Or even better give an answer here so I can close the question? – Parzi Nov 30 '17 at 15:25

2 Answers2

2

you can escape the ' with a preceding '

INSERT INTO skills09 (name, birthday, skills) VALUES ('Tom Haverford', '31_02_1987', 'Being Awesome, Announcing cool things, Treatin'' Yo Self, Failing');

it's basically a dupe of How do I escape a single quote in SQL Server?

if you gave more information on the language you're using or exactly how this sql statement is being formed by the users, I could give more information. for example, you would basically run the user's input through a function that would replace ' with '' (2 single quotes) right before sending it to the sql server.. in the sql server it will be correctly stored as just '

"escaping" the character is just a way for it to not count as the ending single quote, and allows it to be added in the insert.

Abdulla Nilam
  • 36,589
  • 17
  • 64
  • 85
JBoothUA
  • 3,040
  • 3
  • 17
  • 46
  • I am not sure what you are saying here at all sorry. – Parzi Nov 25 '17 at 19:05
  • 1
    i added an example to my answer. it just means to add an extra ' right before the ' that you use in the string. that will "escape" the ' that is in the string, meaning it will not count as the ending '. – JBoothUA Nov 25 '17 at 23:03
  • It is not the correct answer how would you like as a user of a website to need to remember to do a '' every time you want an apostrophe? – Parzi Nov 26 '17 at 23:21
  • And I didn't understand what you were saying because you gave no example and used the wrong word. The word you are looking for is preceding I had no idea what you were trying to say before. – Parzi Nov 26 '17 at 23:25
  • I've given the SQL statement, my question is tagged with PHP and MySQL, and the users can input anything they want. I'm not sure what more information you are trying to ask for. That other StackOverflow was helpful but it still doesn't answer the question. I know how to escape characters on my own. I don't know how to do it to the customer's statement. – Parzi Nov 30 '17 at 15:30
  • Ok, so you are suggesting I put a big notice on my website to warn the customer that if they are going to add an apostrophe they must do ''....... I have never seen this done on any website before so SURLY there is a way to do it. Which IS my question. Per my last comment, I stated I know how to escape a character that I do on my own. I don't know how to escape a character from the customer's statement. From that, you should be able to assume I would like to know how to escape the customer's character. – Parzi Dec 01 '17 at 07:00
  • NO they are not writing SQL statements. Your negative attitude is really not needed or appreciated. As stated in my question I am saving their inputs into variables. If one of those inputs has a single quote it breaks the mySQL query. – Parzi Dec 02 '17 at 08:23
  • "you keep saying it's not a good practice / you've never seen this on a website... That is because behind the scenes the website are escaping the user's inputs before they send them to sql," EXACTLY! HOW!?!?! Stop telling me what's happening and tell me how. I know WHAT needs to be done I need to know HOW. How else can I ask this??? – Parzi Dec 02 '17 at 08:23
  • "or i guess explain why you cannot do what i've said to do?" Because you have not ONCE told me how I escape a character from the user input which is my question! – Parzi Dec 02 '17 at 08:24
-1

PHP How to replace customers text area apostrophes with a escape character

$lastname  = "O'Reilly";
$_lastname = mysqli_real_escape_string($lastname);

$query = "SELECT * FROM actors WHERE last_name = '$_lastname'";
Parzi
  • 694
  • 2
  • 10
  • 33