Hướng dẫn mysql allow special characters

Your question is a fine collection of confusions. You managed to confuse everything.
Let's try to sort it out.

Nội dung chính

• How can I insert special characters in MySQL using PHP?
• How do I allow special characters in PHP?
• How do you store special characters in a database?
• How do you insert special characters in SQL?
• Not the answer you're looking for? Browse other questions tagged php mysql utf-8 special-characters or ask your own question.
• How to enable UTF
• What is UTF
• How to escape HTML special characters in PHP?
• What is MySQL_ real_ escape_ string in PHP?

Nội dung chính

• How can I insert special characters in MySQL using PHP?
• How do I allow special characters in PHP?
• How do you store special characters in a database?
• How do you insert special characters in SQL?

Using PHP, what is the best way to store special characters (like the following) in a MSQUL database, to avoid injections.

These are incomparable matters. Storing special chars is one matter and avoiding injections is another. completely different one.

$book_text=htmlentities($book_text, "ENT_QUOTES");

this is most funny part. although it is intended to protect your queries, it's actually does nothing. Because instead of constant ENT_QUOTES which value is 3 you are using string ENT_QUOTES which numeric value is 0, so, you are setting no flag.

But even if you set this flag correctly, it won't automatically protect you. Because an injection code may contain no special chars.

To avoid injections, you have to follow whole set of rules, not one simple function "make_my_data_safe()". There is no magic wand.
See this my answer for the details.

As for the specialchars, it's simple. The only problem that there are NO solid special chars set. There are different special chars for the different environments.

• ' have meaning for the database and HTML
• <> have meaning for the HTML only
• é à ù have meaning for the HTML only, depends on the encoding.

you have you use different formatting rules for the every case. Different, not a single one for all.

to use é à ù chars with HTML you have to set proper HTTP header. to use é à ù with database you have to set your table encoding to utf8 and connection encoding to utf 8.

How can I insert special characters in MySQL using PHP?

Use mysqli_real_escape_string() to Insert Special Characters Into a Database in PHP. To get user input with special characters from the form fields, we use the mysqli_real_escape_string() function. We need the following parameters: database connection and the strings we want to escape.

How do I allow special characters in PHP?

Tip: To convert special HTML entities back to characters, use the htmlspecialchars_decode() function..

& (ampersand) becomes &.

" (double quote) becomes ".

' (single quote) becomes '.

< (less than) becomes <.

> (greater than) becomes >.

How do you store special characters in a database?

For text that could have "special" characters, you need nvarchar . +1 Also it should be noted that it's unless you're expecting to allow "special characters" or "foreign language characters", you'll probably want to stick with a varchar especially if you're going to index by that column.

How do you insert special characters in SQL?

Solution 3.

select * from table where myfield like '%15\% off%' ESCAPE '\'.

set @myString = replace( replace( replace( replace(@myString,'\','\\'), '%','\%'), '_','\_'), '[','\[').

select * from table where myfield like '%' + @myString + '%' ESCAPE '\'.

I Have a form with one textbox called(ProductTitle)

Nội dung chính

• Not the answer you're looking for? Browse other questions tagged php mysql utf-8 special-characters or ask your own question.
• How to enable UTF
• What is UTF
• How to escape HTML special characters in PHP?
• What is MySQL_ real_ escape_ string in PHP?

if I write as example "Étuit" in the textbox and click on Save, I post the data in a table called Product. The result int the database for ProductTitle is Étuit. My concern is about the Special character. Instead of putting É in the database , I got that É

When I Load the Product Title ("Étuit") from the database into a span. That show correctly.
BUT When I load it inside a Textbox to Edit the Product Title, that show Étuit.

Anybody know why.

I Put that in the html head

Note : When I Click save on the form, the data is posted with jquery ajax method.

asked Aug 16, 2011 at 3:38

Jean-FrancoisJean-Francois

1,8894 gold badges35 silver badges72 bronze badges

3

Try seting the client encoding before using the DB.

mysql_query("SET NAMES 'utf8'");

If the above doesn't work use the utf8 encode/decode functions:

answered Aug 16, 2011 at 3:52

Pedro LobitoPedro Lobito

88.2k29 gold badges238 silver badges256 bronze badges

8

Neuron

4,5284 gold badges32 silver badges53 bronze badges

answered Aug 16, 2011 at 3:42

AlienWebguyAlienWebguy

75.9k17 gold badges120 silver badges144 bronze badges

5

Probably what is happening is that the default character set for the client is not set to UTF-8, so you're getting tranposition in one direction or the other. This is covered in a number of different ways here:

Often an initialization query of "SET NAMES utf8" just after the connection is instantiated will solve the issue going forward but make sure that what you think is stored (utf8) is actually what was stored. You might have a cleanup job if not.

answered Aug 16, 2011 at 3:59

3

Not to bother with SET NAMES before every connection in the code, a parameter in mysql connection string can be used:

"jdbc:mysql://hostAddress:port/databaseName?characterEncoding=UTF-8"

answered Nov 3, 2013 at 13:36

ZonZon

16.7k6 gold badges83 silver badges94 bronze badges

This post explains how to configure and work with UTF-8 in PHP and MySQL. Hope that saves your time.

A UTF-8 Primer for PHP and MySQL

answered Apr 17, 2014 at 15:32

Dmitry PavlovDmitry Pavlov

29.1k8 gold badges99 silver badges115 bronze badges

These work for me:

In the HTML headers:

After the PHP connection:

$conexion = @mysql_connect($servidor, $usuario, $contrasenha);
mysql_select_db($BD, $conexion) or die(mysql_error($conexion));
mysql_query("SET NAMES 'utf8'");

answered Feb 10, 2016 at 22:03

lmcDevloperlmcDevloper

3322 silver badges8 bronze badges

I just use set_charset method when i'm using mysqli lib.

error_reporting(E_ALL);

$mysqli = new mysqli('localhost', 'login', "pass", 'database');

if ( ! $mysqli->set_charset("utf8") )
{
   printf("Error loading character set utf8: %s\n", $mysqli->error);
}

answered Aug 22, 2017 at 22:54

I also had difficulties with this, but the following always works for me ! Before manipulating your data, make sure to set the encoding as follows:

try{
  $dbh = new PDO($dsn, $user, $pass);
  $dbh->query("SET NAMES 'utf8'");
  print "Connected";
 }

catch(PDOException $e){
  print "Error!!   " . $e->getMessage()."
";
  die();
 }

answered Aug 18, 2018 at 2:10

Not the answer you're looking for? Browse other questions tagged php mysql utf-8 special-characters or ask your own question.

How to enable UTF

The first thing you need to do is to modify your php. ini file to use UTF-8 as the default character set: default_charset = "utf-8"; (Note: You can subsequently use phpinfo() to verify that this has been set properly.)

What is UTF

Definition and Usage. The utf8_encode() function encodes an ISO-8859-1 string to UTF-8. Unicode is a universal standard, and has been developed to describe all possible characters of all languages plus a lot of symbols with one unique number for each character/symbol.

How to escape HTML special characters in PHP?

The predefined characters are:.

& (ampersand) becomes &.

" (double quote) becomes ".

' (single quote) becomes '.

< (less than) becomes <.

> (greater than) becomes >.

What is MySQL_ real_ escape_ string in PHP?

Definition and Usage. The real_escape_string() / mysqli_real_escape_string() function escapes special characters in a string for use in an SQL query, taking into account the current character set of the connection. This function is used to create a legal SQL string that can be used in an SQL statement.