I want to insert a special character &quot;like 1350-28-1¼&quot; FVL&quot; in MySQL using insert query in PHP. I tried with real_escape_string() but it inserts &quot;1350-28-1Â¼&quot; FVL&quot; in table. I also tried with htmlentities() but it inserts &quot;1350-28-1 &amp; frac14; &amp; quot; FVL&quot; in table. How do I insert this type of special character in a MySQL database table using PHP?
326
I'm setting up a new server and want to support UTF-8 fully in my web application. I have tried this in the past on existing servers and always seem to end up having to fall back to ISO-8859-1.  Where exactly do I need to set the encoding/charsets? I'm aware that I need to configure Apache, MySQL, and PHP to do this — is there some standard checklist I can follow, or perhaps troubleshoot where the mismatches occur?  This is for a new Linux server, running MySQL 5, PHP, 5 and Apache 2.
40403
Quick add on requirement in our project.  A field in our DB to hold a phone number is set to only allow 10 characters.  So, if I get passed "(913)-444-5555" or anything else, is there a quick way to run a string through some kind of special replace function that I can pass it a set of characters to allow?  Regex?
58273
I know that php has md5(), sha1(), and the hash() functions, but I want to create a hash using the MySQL PASSWORD() function.  So far, the only way I can think of is to just query the server, but I want a function (preferably in php or Perl) that will do the same thing without querying MySQL at all.  For example:  MySQL hash -> 464bb2cb3cf18b66  MySQL5 hash -> *01D01F5CA7CA8BA771E03F4AC55EC73C11EFA229  Thanks!
60603
I have a user with a query from an SQL Database that returns a series of string in this form:  000123123.23000 000123123.23000 000123123.23000 000123123.23000 000123123.23000   These are really numbers, however they cannot be summed.  The correct way to fix this might be to cast the data in the SQL query, but then the Excel query viewer can't display it. As a workaround I do    = &lt;CELL&gt; +1 -1.   Is there a more elegant solution?
19369
points out the following security problem with MySQL:  USERS id | email            | password_reset_token 1  | one@example.com  | QTlXww)uV!Hdg3U6aGwKV2FPvAqzVgPx 2  | two@example.com  | CZor5t7WbX#LTeqiG3v@6f3@z#)BfK*n   Here we have nice, random tokens that a user must prove they have in order to reset their account. But the user manages to submit a reset token of 0, so we run this query:  SELECT * FROM `users` WHERE `email` = 'one@example.com' AND `password_reset_token` = 0   MySQL converts the token VARCHAR to an INT in order to make the comparison. It considers a VARCHAR that does not start with a number to equal 0. Therefore, an attacker can match any non-numeric string and take over the account.  Even if the reset token starts some numbers, the situation is not much better. MySQL ignores all characters but the initial numbers when making this comparison, so it considers 12blahblah3blah4 to be equal to 12, which makes guessing exponentially easier.  Can I configure MySQL not to do this kind of typecasting? If, for example, it cast INT to VARCHAR instead of vice versa, this attack would not work.  Note  If the query is run with '0' instead of 0, this doesn't work. The article discusses this vulnerability in terms of Ruby on Rails' acceptance of XML, where a type=integer property convinced Rails to send an actual integer in the query.  That bug has been patched in Rails; it now converts all request parameters to strings, so it would never build a query with an integer. But I still think MySQL should be configurable to prevent this.
62223
In MySQL we can create queries with or without the backtick (`) symbol. Example:   SELECT * FROM TEST; SELECT * FROM `TEST`;   Both works fine in mysql-console.  Is there any technical difference between them?  Is there any benefit using (`) over over simple queries?
115935
I have a form with a textarea. Users enter a block of text which is stored in a database.  Occasionally a user will paste text from Word containing smart quotes or emdashes. Those characters appear in the database as: â€“, â€™, â€œ ,â€  What function should I call on the input string to convert smart quotes to regular quotes and emdashes to regular dashes?   I am working in PHP.  Update: Thanks for all of the great responses so far. The page on Joel's site about encodings is very informative:   Some notes on my environment:  The MySQL database is using UTF-8 encoding. Likewise, the HTML pages that display the content are using UTF-8 (Update:) by explicitly setting the meta content-type.  On those pages the smart quotes and emdashes appear as a diamond with question mark.  Solution:  Thanks again for the responses. The solution was twofold:   Make sure the database and HTML files were explicitly set to use UTF-8 encoding. Use htmlspecialchars() instead of htmlentities().
61016
I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping any single quotes and surrounding the whole string with single quotes. Here's the code:  sSanitizedInput = "'" &amp; Replace(sInput, "'", "''") &amp; "'"   Any single-quote the user enters is replaced with double single-quotes, which eliminates the users ability to end the string, so anything else they may type, such as semicolons, percent signs, etc., will all be part of the string and not actually executed as part of the command.    We are using Microsoft SQL Server 2000, for which I believe the single-quote is the only string delimiter and the only way to escape the string delimiter, so there is no way to execute anything the user types in.  I don't see any way to launch an SQL injection attack against this, but I realize that if this were as bulletproof as it seems to me someone else would have thought of it already and it would be common practice.    What's wrong with this code? Is there a way to get an SQL injection attack past this sanitization technique?  Sample user input that exploits this technique would be very helpful.    UPDATE:  I still don't know of any way to effectively launch a SQL injection attack against this code. A few people suggested that a backslash would escape one single-quote and leave the other to end the string so that the rest of the string would be executed as part of the SQL command, and I realize that this method would work to inject SQL into a MySQL database, but in SQL&nbsp;Server 2000 the only way (that I've been able to find) to escape a single-quote is with another single-quote; backslashes won't do it.  And unless there is a way to stop the escaping of the single-quote, none of the rest of the user input will be executed because it will all be taken as one contiguous string.  I understand that there are better ways to sanitize input, but I'm really more interested in learning why the method I provided above won't work. If anyone knows of any specific way to mount a SQL injection attack against this sanitization method I would love to see it.
39428
I'm looking to find records in a table that match a specific number that the user enters.  So, the user may enter 12345, but this could be 123zz4-5 in the database. I imagine something like this would work, if PHP functions worked in MySQL. SELECT * FROM foo WHERE preg_replace(&quot;/[^0-9]/&quot;,&quot;&quot;,bar) = '12345'  What's the equivalent function or way to do this with just MySQL? Speed is not important.
86789
I don't have this kind of ID in my table, still MySQL is giving me a result.  This same result is giving when i place in my php code.
115591
In a fairly unique situation, my team has ended up with UTF-8 bytes in a database that thinks the data is encoded as latin1.  At least, I'm 85% certain that this is the situation at hand.  For example, a  was handed to the database by  and just treated the data as raw bytes (0xE2 0x80 0x99). This data, as far as I can tell (how to verify?), was stored as those actual bytes. So now when the data is read out by a more intelligent programming language (Ruby 1.9), the database helpfully says , and so instead of "Mike’s", we end up with "Mikeâ€™s". This is also what I get in the mysql prompt when SELECTing that value.  So, essentially, we have a bunch of utf-8 encoded data stored in a database that thinks the data is encoded as latin1.  This makes me to somehow tell the database "No, no matter what you think, this stuff is actually utf-8". CONVERT TO doesn't seem like the right tool, because then I'll end up with permanent "Mikeâ€™s".    Failed/moronic attempt #1  I noticed this:  &gt; SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name            | Value             | +--------------------------+-------------------+ | character_set_client     | utf8              | | character_set_connection | utf8              | | character_set_database   | utf8              | | character_set_filesystem | binary            | | character_set_results    | utf8              | | character_set_server     | latin1            | | character_set_system     | utf8              | | collation_connection     | utf8_general_ci   | | collation_database       | utf8_unicode_ci   | | collation_server         | latin1_swedish_ci | +--------------------------+-------------------+   And thought that maybe changing character_set_results to latin1 would trick it into not doing any conversion of the bytes, resulting in the proper display of data on my utf8 OS.  Sure enough, SET character_set_results=latin1; results in ’ instead of â€™. Cool!  So I added this to my ~/.my.cnf (which is the only my.cnf, I checked):  [mysqld] ... character-set-results=latin1   and when I go back to the MySQL prompt &amp; check the character_set_% variables, it's still utf8.  Yes, it just occurred to me that mysqld is a deamon, which means I probably need to restart the whole mysql process for this to take effect. But whoever installed MySQL on this machine used the dmg instead of the brew (wasn't me!) and the MySQL pref pane is currently telling me that MySQL isn't running even though it clearly is, and anyhow before I go down that rabbit hole, I want to check with an actual DBA and see how ridiculous this is, or if there's just a better, cleaner way to do it.
836
I'm writing a php program that pulls from a database source. Some of the varchars have quotes that are displaying as black diamonds with a question mark in them (�, , I assume from Microsoft Word text).  How can I use php to strip these characters out?
31406
I am planning to replace repeatedly executed Statement objects with PreparedStatement objects to improve performance. I am using arguments like the MySQL function now(), and string variables.   Most of the PreparedStatement queries I have seen contained constant values (like 10, and strings like "New York") as arguments used for the ? in the queries. How would I go about using functions like now(), and variables as arguments? Is it necessary to use the ?s in the queries instead of actual values? I am quite confounded.
87331
When I Import Mysql from local server to web ser it is showng me error 1115 - Unknown character set: 'utf8mb4' in Creating wordpress wp_comment table CREATE TABLE IF NOT EXISTS  `wp_commentmeta` ( // // ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 AUTO_INCREMENT =1;  #1115 - Unknown character set: 'utf8mb4'  My mysql version is Version information: 4.0.4, latest stable version: 4.4.10 (local version)  Version information: 4.0.10.7, latest stable version: 4.4.10( server version)  Please help me to solve out the error
113299
I'm trying to learn a bit more about the details of proper database construction and ran across something that confuses me. In a current Wordpress installation, I'm finding varchar fields used for indexes which are longer than the 191 characters, which the documentation seems to say is the max. I'm running MariaDB 10.x which I believe matches up with MySQL 5.7, and the  confirms the limit when using utf8mb4_unicode_ci collation and compact as the format.  I'm finding varchar(200) and varchar(255) indexed fields, e.g. wp_postmeta, the field meta_key is varchar(255). Am I wrong or confused on this? The db is using the InnoDB engine. Thanks for any clarification.
17325
I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:  abad abád  I am using a query that looks like this:  SELECT *  FROM `words` WHERE `word` = 'abád'   The query result gives both words:  abad abád  Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return    abád    I have also tried this query:  SELECT *  FROM `words` WHERE BINARY `word` = 'abád'   It gives me no results. Thank you for the help.
14520
For example, mysql quote table name using   SELECT * FROM `table_name`;   notice the `   Does other database ever use different char to quote their table name