Error deleting record when a field contains apostrophe

PHP
1

Hello,

delete from users where userid= $id;

I have a php script which allows the user to delete a record.
However, if one of the fields contains an apostrophe
for example: O’Malley

My query crashes. How do I get around this?

2

Hi,
You can use


mysql_query("delete from users where userid=".stripslashes($id)."");

This will add a slash to the quote and the query will not crash. however you can refer to these links:
http://php.net/manual/en/function.addslashes.php
http://php.net/manual/en/function.stripslashes.php

3

Just wanted to reiterate the dangers of not escaping input. Just google SQL injection, there’s a lot of information out there.

A quick example


DELETE FROM `users` WHERE `name` = 'O'Malley'

The above example will fail, no big deal aye? Imagination the user was called ’ OR ‘1’ = '1

Now that query again with our new user name


DELETE FROM `users` WHERE `name` = '' OR '1' = '1'

Good bye user table!

Simple using mysql_real_escape on all input will protect you from this.

4

It seems the OP has a table having the userid column/field of varchar/string type not the integer which is itself not a good idea. Convert it or create another field of integer and use that field to delete.

Otherwise the query should work pretty well.

As cranial-bore, I would also suggest to sanitize any of the user input values (whether it is from query string or form) before you use them in the SQL query. GOPalmer has explained pretty clearly how an SQL statement can be injected to harm your database. So I would prefer using mysql_real_escape_string() rather than addslash() function if you are in mysql.

5

First of all, if the apostrophe (or field value) is only present in the DB data, and not in your SQL statement it shouldn’t matter.
What does your SQL look like?

Secondly this is database interaction 101. You need to escape values before using them in an SQL query. What’s happening is the apostrophe in O’Malley is being confused by MySQL as being part of the query.
Same thing can happen with regular PHP:


$name = 'O'Malley';

Use [fphp]mysql_real_escape_string[/fphp] to escape values before using them in SQL. Although if you are deleting by id you shouldn’t need the value in there at all.
Example:


DELETE FROM my_table WHERE id = 256

Kath, can I ask if there was a tutorial or some resource you were using to get this far?