tags:

views:

68

answers:

4

I have a textarea in a form, when I enter special characters in it, I get an error in mysql. (when submitting the form to a php-file which does the work of inserting into mysql)

I need to know exactly what characters that aren't allowed, or easier would be, exactly what characters thar ARE allowed, so that I could validate the textarea before submitting.

Does anybody know?

I have tried mysql_real_escape_string() but didn't help...

NOTE: In the textarea, users are supposed to enter some special chars like these:

 + , . ; : - _ space & % ! ? = # * ½ @ / \ [ ] ' " < > £ $ €

Probably got them all...

how can I do this?

Thanks

UDPATE

My mysql_query :

mysql_query("INSERT INTO cars_db (description) VALUES ('$ad_text')");

UPDATE

Mysql error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a"a!a?aa+a-a_a
a/a\a[a]a}a{a&a%a#a@a¨a^a*a*aa,a.a:a;a|a½a
§a' at line 1
+4  A: 

A database column can technically hold any of those characters. The problem is that you are not escaping them properly in your query.

One way way to do this using mysql_real_escape_string is as follows:

$sql=sprintf("insert into cars_db (description) values ('%s')",
    mysql_real_escape_string($_POST['description']) );

//execute query and show errors that result...
$result = mysql_query($sql);
if (!$result) {
    die("Oops:<br>$sql<br>".mysql_error());
}

Another way is to use a library like PDO or ADODb which makes it easier to use prepared statements with placeholders. Such libraries ensure that data injected into queries is properly escaped.

This is good practice not only because it solves your problem, but it also improves the security of your code, since it becomes harder to perform SQL injection attacks.

Paul Dixon
check my update plz
Camran
your update doesn't really show how you constructed the $ad_text value
Paul Dixon
what do you mean how I constructed it, its just a textarea, where users put in whatever text, and it gets submitted to phpfile, and fetched using the $_POST in php... thats it
Camran
your post says you tried mysql_real_escape_string, but doesn't make it clear how.
Paul Dixon
oh, like this: $ad_text=mysql_real_escape_string($ad_text);
Camran
ok, so what error does your code produce? do you display mysql_error() in the event of failure?
Paul Dixon
check my update again plz
Camran
What Paul is trying to aks you is: WHERE IS EXACLTY VALORIZED $ad_text? Somewhere, you should have something like: $ad_text = $_POST['textarea_name'];, and then your query.Replace your query with: mysql_query("INSERT INTO cars_db (description) VALUES ('".mysql_real_escape_string($ad_text)."')"); and should be fine... well, maybe not fine, but better. Oh, replace 'textarea_name' with the name of your textarea.
DaNieL
I got it,,,, I previously used mysql_real_escape_string AFTER the variable was fetched with POST... but changing the code so that it does it at the same time like DaNieL proposed solved it.
Camran
Eh, mysql_real_scape_string DONT return the 'escaped string', people get confising many times ;)
DaNieL
+3  A: 

Another way would be to use prepared statements. This makes sure SQL injection isn't possible.

Matthias Vance
A: 

Do this:

$ad_text = mysql_real_escape_string($ad_text);
mysql_query("INSERT INTO cars_db (description) VALUES ('$ad_text')");

Read up on mysql_real_escape_string and SQL injection. This is a massive security hole in your application.

http://us.php.net/mysql%5Freal%5Fescape%5Fstring

Scott Saunders
but I have tried mysql_real_escape_string()... read the post
Camran
mysql_real_escape_string does not alter its operand, this code would not work.
Paul Dixon
doh! Thank you Mr. Dixon. I've corrected my post, and upvoted yours.
Scott Saunders
better :) You might want to lose the extra closing parentheses on the first line though..
Paul Dixon
:) Reading this, you'd never guess I've written this code thousands of times.
Scott Saunders
A: 

Instead of escaping characters so as not to trip up your query, why not create a stored procedure with an incoming String parameter. Just pass the form variable's value (or save it to a string) and pass that to the stored procedure.

adamcodes