CODEWITHSUNDEEP
phpmysqldatabaseinsertspecial-characters
Joey Morani
Joey Morani

Reputation: 26611

How to insert special characters into a database?

Can anyone tell me how to insert special characters into a MySQL database? I've made a PHP script which is meant to insert some words into a database, although if the word contains a ' then it wont be inserted.

I can insert the special characters fine when using PHPmyAdmin, but it just doesn't work when inserting them via PHP. Could it be that PHP is changing the special characters into something else? If so, is there a way to make them insert properly?

Upvotes: 18

Views: 128258

Answers (13)

Santhosh
Santhosh

Reputation: 1

$insert_data = addslashes($_POST['username']);

You can use this method.

Upvotes: 0

xcoders
xcoders

Reputation: 1

I put it here for future reference. After wasting 20 minutes i got a solution to put special characters to database. we do not have to use mysql_real_escape_string($holdvalue) like that. we have to do this in this way. $db->real_escape_string($holdvalue). Where $db is the databse connection details. $db = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);.

Upvotes: 0

Solomon Sudhir
Solomon Sudhir

Reputation: 1

htmlspecialchars function is the best solution fellows. Today I was searching for how to insert strings with special characters and the google thrown so many Stackoverflow listings.None of them provided me solution. I found it in w3schools page. Yes I could solve my problem by using this function like this:

$abc = $POST['xyz'];

$abc = htmlspecialchars($abc);

Upvotes: 0

Nɪsʜᴀɴᴛʜ ॐ
Nɪsʜᴀɴᴛʜ ॐ

Reputation: 2914

For Example:
$parent_category_name = Men's Clothing
Consider here the SQL query

$sql_category_name = "SELECT c.*, cd.name, cd.category_id  as iid FROM ". DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = '" .  $this->db->escape($parent_category_name) . "' AND c.parent_id =0 ";

Error:

Static analysis:

1 errors were found during analysis.

Ending quote ' was expected. (near "" at position 198)
SQL query: Documentation

SELECT c.*, cd.name, cd.category_id as iid FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = 'Men's Clothing' AND c.parent_id =0 LIMIT 0, 25

MySQL said: Documentation

#1064 - 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 's Clothing' AND c.parent_id =0 LIMIT 0, 25' at line 1

Try to implement:

$this->db->escape($parent_category_name)

The above method works on OpenCart framework. Find your framework & implement OR mysql_real_escape_string() in Core PHP

This will become Men\'s Clothing i.e, In my case

$sql_category_name = "SELECT c.*, cd.name, cd.category_id  as iid FROM ". DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = '" .  $this->db->escape($parent_category_name) . "' AND c.parent_id =0 ";

So you'll get the clear picture of the query by implementing escape() as

SELECT c.*, cd.name, cd.category_id as iid FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) WHERE cd.name = 'Men\'s Clothing' AND c.parent_id =0

Upvotes: 0

Soyab Badi
Soyab Badi

Reputation: 400

Use this: htmlentities($_POST['field']);

Enough only. This for special character:

Use for CI htmlentities($this->input->post('control_name'));

Upvotes: 0

amarjit singh
amarjit singh

Reputation: 461

$var = mysql_real_escape_string("data & the base");
$result = mysql_query('SELECT * FROM php_bugs WHERE php_bugs_category like  "%' .$var.'%"');

Upvotes: 0

symcbean
symcbean

Reputation: 48387

Note that as others have pointed out mysql_real_escape_string() will solve the problem (as will addslashes), however you should always use mysql_real_escape_string() for security reasons - consider:

SELECT * FROM valid_users WHERE username='$user' AND password='$password'

What if the browser sends

user="admin' OR (user=''"
password="') AND ''='"

The query becomes:

SELECT * FROM valid_users 
WHERE username='admin' OR (user='' AND password='') AND ''=''

i.e. the security checks are completely bypassed.

C.

Upvotes: 3

Peter Parker
Peter Parker

Reputation: 29735

use mysql_real_escape_string

So what does mysql_real_escape_string do?

This PHP library function prepends backslashes to the following characters: \n, \r, \, \x00, \x1a, ‘ and “. The important part is that the single and double quotes are escaped, because these are the characters most likely to open up vulnerabilities.

Please inform yourself about sql_injection. You can use this link as a start

Upvotes: 6

user30932
user30932

Reputation:

You are propably pasting them directly into a query. Istead you should "escape" them, using appriopriate function - mysql_real_escape_string, mysqli_real_escape_string or PDO::quote depending on extension you are using.

Upvotes: 5

Paul Lammertsma
Paul Lammertsma

Reputation: 38292

You are most likely escaping the SQL string, similar to:

SELECT * FROM `table` WHERE `column` = 'Here's a syntax error!'

You need to escape quotes, like follows:

SELECT * FROM `table` WHERE `column` = 'Here\'s a syntax error!'

mysql_real_escape_string() handles this for you.

Upvotes: 6

OM The Eternity
OM The Eternity

Reputation: 16244

Probably "mysql_real_escape_string()" will work for u

Upvotes: 2

Anthony
Anthony

Reputation: 37085

$insert_data = mysql_real_escape_string($input_data);

Assuming that you have the data stored as $input_data

Upvotes: 32

zaf
zaf

Reputation: 23274

Are you escaping? Try the mysql_real_escape_string() function and it will handle the special characters.

Upvotes: 12

Related Questions