I'm working on an app with MySQL database. I'm getting odd behavior with phone numbers that start with a plus sign e.g. +13215555555
The issue, of course, is that I'm using Google Firebase phone authentication for part of the project. Firebase stores phone numbers as +13215555555 (Country code plus phone number)
I'm easily able to push phone numbers with the + format to the database. What I'm unable to do is use a phone number with a plus sign to query the record.
Sample data:
id user phone email
1 Abe +13215555555 abe@fake_email.com
2 Beth 13215555556 [email protected]
3 Charl 321-555-5557 [email protected]
Data in:
const sql = " INSERT INTO `users` ( `user`, `phone`, `user_email` ) +
" VALUES (?,?,?)";
const dataReturn = await promisePool.execute(sql, dataArray);
Any of the phone number formats above works great for inserting data into the database. The plus sign +3215555555 works great. And what I see above is what is observed in Adminer. The phone data field is structured as varchar(20).
Data Out:
What I'm unable to do is grab a user record via the phone number when the phone number starts with a plus sign.
const sql_ui = "SELECT * FROM `users` WHERE `phone` = ? "
const data_return_ui = await promisePool.execute(sql_ui, [ph_string_value])
In this case, I'm unable to get any user result with the value of ph_string_value = "+13215555555" Do note, when I manually create a SQL statement
SELECT * FROM `users` WHERE `phone` = +13215555555
I get exactly the expected results when tested in Adminer. What doesn't work is the query from my program with the ph_string_value with a leading plus sign in the contents. I get a null response to the data set. I'd like to understand the cause of this issue.
Why do queries using a phone number that start with a plus sign fail?
(edit)
Note: When I do a console.log("ph_string_value: ", ph_string_value) immediately before the query, I do see "+13215555555" as expected..
I'm trying to build a simple reproducible issue, using just SQL...
CREATE TABLE `users_SO` (
`id` int NOT NULL AUTO_INCREMENT,
`user` varchar(30) NOT NULL,
`phone` varchar(20) NOT NULL,
`email` varchar(40) DEFAULT NULL,
PRIMARY KEY (`phone`),
KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
INSERT INTO `users_SO` ( `user`, `phone`, `email`) VALUES
("Abe", "+13215555555", "abe@fake_email.com"),
("Beth", "13215555556", "[email protected]"),
("Charl", "321-555-5557", "[email protected]")
SET @ph_string_value = "+13215555555" ;
# Verify the variable holds what we think it holds...
SELECT @ph_string_value as ph_string_value;
SELECT * FROM `users_SO` WHERE `phone` = @ph_string_value;
SET @ph_string_value2 = "13215555556"
# Verify the variable holds what we think it holds...
SELECT @ph_string_value2 as ph_string_value2;
SELECT * FROM `users_SO` WHERE `phone` = @ph_string_value2
Initially odd results at Adminer SQL tester. Not sure I understand how to use https://dbfiddle.uk/?rdbms=mysql_8.0 Their help screen isn't much help.
Update, now the SQl when tested in Adminer works great. No errors. When tested over at db-fiddle dot com/ I get an odd error:
Query Error: Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='
Question: When setting up the database, which collation setup seems the most robust?
OOps. At one point I do pass the phone number in a fetch call to an internal API routing.
async function get_user_info(ph_num) {
try {
let response = await fetch(
ENV_OBJ.WEB_URL +
"/api/mysql_connect?getType=user_info&ph="+ph_num <-- Fail
);
let user = await response.json();
if (response.ok) {
console.log("user: ", user)
return user;
}
} catch (err) {
console.log("error from get_user_info(): ", err);
}
}
And yes, and on arrival at the API server, the plus sign has disappeared from the intended phone string value.
The fix?
ENV_OBJ.WEB_URL + "/api/mysql_connect?getType=user_info&ph="+encodeURIComponent(ph_num)