5 Replies - 369 Views - Last Post: 04 February 2013 - 12:36 PM Rate Topic: -----

#1 DoxramosPS  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 171
  • Joined: 07-October 12

Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 11:14 AM

I'm trying to make a form where users can submit bugs that are then inserted into a MySQL database.
The problem is that anytime someone try's to use an apostrophe it will break the SQL statement inside the PHP and they recieve a SQL syntax error for the apostrophe and everything past it.
IE
User is saying:
I've attempted.
They will get You have an error in your SQL syntax.....
Check the right syntax to use near 've attempted at line 3.

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting user inputted data with apostrophe's.

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 11:24 AM

You are inserting data directly to the database without sanitising it, leaving yourself open to SQL injection attacks.

Use mysqli_real_escape_string() (depending on which database library you are using) or MUCH BETTER use parameterized queries. The docs.

This post has been edited by andrewsw: 04 February 2013 - 11:24 AM

Was This Post Helpful? 2
  • +
  • -

#3 DoxramosPS  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 171
  • Joined: 07-October 12

Re: Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 11:42 AM

Thank you. Followed the guide and a lot easier than expected to use mysqli escape string.

On a side note. Even though there's drop boxes involved as well. Is it still beneficial to use mysqli_real_escape_string?

This post has been edited by DoxramosPS: 04 February 2013 - 11:43 AM

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 12:05 PM

I assume you mean a drop-down list (SELECT tag)? Doesn't make any difference: any data that you want to insert into the database should be run through mysqli_real_escape_thing.

I still recommend that you investigate prepared statements. They are much more secure and avoid the kinds of hassle you had with apostrophes.
Was This Post Helpful? 0
  • +
  • -

#5 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3718
  • View blog
  • Posts: 5,989
  • Joined: 08-June 10

Re: Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 12:35 PM

Quote

On a side note. Even though there's drop boxes involved as well. Is it still beneficial to use mysqli_real_escape_string?

Keep in mind that it's extremely easy to make a custom HTTP request that includes whatever nonsense you want instead of the form data your site is expecting. Just because you provide a list of expected values in a <select> box, you can not assume that you will only ever get those values from the user. You need to be expecting that some of your users will be trying to damage your site by providing invalid data. - (And causing SQL queries to fail because of extra apostrophes is the least of your worries in that regard. A well written injection attack on a vulnerable site could very well delete the entire database.)

Always expect your users to be either A) Complete idiots that can't follow the simplest, most obvious instructions, or B) Genius hackers trying their best to destroy your site.

For both cases you need to validate the data and make sure it's what you are expecting it to be, whether that involves comparing it to a list of options or that it adheres to a set format. And for the B cases you need to sanitize the data and otherwise take all possible security precautions. Security should never be taken lightly. If you haven't gone out of your way already to learn as much as you can about that subject, you should.

And I also very much agree with andrewsw that you should read up on prepared statements instead of escaping the input data. It's superior in that it doesn't just try to prevent SQL injection like escaping does, it manages to avoid it completely.
Was This Post Helpful? 1
  • +
  • -

#6 CTphpnwb  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2957
  • View blog
  • Posts: 10,181
  • Joined: 08-August 08

Re: Inserting user inputted data with apostrophe's.

Posted 04 February 2013 - 12:36 PM

Escaping strings is only necessary if you're using outdated, insecure MySQL functions. You should NOT be. Here's a great tutorial on PDO. Read it. Study it. Use it. It will be much easier and much more secure.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1