MySQL and PHP not inserting rows with spaces or special characters

Question

I have an Android App that I program, when users search parts, my php document on my web server I have setup to add what they searched into another table to just tack how many searches are being done through the app, when a field has no spaces it adds to the table just fine, however if the field has a space or special chars in it like a dash, it dose not insert into the table. I'm at a loss how to get this to work properly and I'm hoping someone might some insight into what I'm doing wrong here. Thank you all in advanced :)

PHP Code Server Side (MySql DB)

$sql = "INSERT INTO USAGE_STATS (ITEM_NAME, FSC, NIIN, PART_NUMBER)
VALUES ('$ITEM_NAME', '$FSC', '$NIIN', $PART_NUMBER)";

if ($conn->query($sql) === TRUE) {
    $row = mysqli_insert_id($conn);
} else {
    //do nothing
}

The colation is set to "utf8_general_ci," feilds are set to "Text," and the datebase is a MyISAM

Edit: Just tried another part, and the full part number is "5-86510-53" but when it was inserted into the table it just came out to "-86558" in the DB

UPDATE EDIT: No this has not been answered before, I was not asking how to prevent SQL injection attacks, I was asking what I was doing wrong here.... offering advice that I should do prepared statements is the kind of advice I was looking for. I want to thank everyone for pointing me in the right direction, but marking me down for asking a question about what I was doing wrong is not what I thought this site was supposed to be about... but thank you again to those who offered meaningful assistance :)

Use prepared statements. They will solve all your insertion issues and protect you from SQL injection at the same time. See the [manual](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) — Nick, May 27 '19 at 03:28
Do you have some example code you could show me how to go about it? I usually just program in Java for Android OS, and just work with PHP and MySQL when I really need to. — , May 27 '19 at 03:31
At the very least, being a text field, you need single quotes around $PART_NUMBER and as Nick mentioned , either prepared statements or use mysqli_real_escape_string to prevent SQL injection. — Ryan, May 27 '19 at 03:57
@AndroidPrg91 if you follow the link to the manual there is a lot of sample code there. Also on the pages for [`mysqli::prepare`](https://www.php.net/manual/en/mysqli.prepare.php) and [`mysqli_stmt::execute`](https://www.php.net/manual/en/mysqli-stmt.execute.php) — Nick, May 27 '19 at 04:14
`echo '5-86510-53' | bc -86558 ` so it looks like in your example that part number is being treated like a number and then the result is inserted. — davejagoda, May 27 '19 at 04:19
Possible duplicate of [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Dharman, May 27 '19 at 05:53
Your basic mistake is treating non-numerical data, as if it was purely numeric. _“and the full part number is "5-86510-53" but when it was inserted into the table it just came out to "-86558" in the DB”_ - that’s because you told the database to do the _calculation_ 5 minus 86510 minus 53. This is _textual_ data, not numeric data, so it needs to be in _quotes_, when inserted into an SQL query directly. (Prepared statements solve this differently.) — 04FS, May 27 '19 at 06:56
@Dharman Not a duplicate post, I was not asking how to prevent SQL injection attacks, I was simply asking what I was doing wrong in my existing code, and some ways I could do it better. But thank you for commenting on my post, it dose mean alot to see programmers helping other programmers :) — , May 28 '19 at 00:35
@04FS THANK YOU! that was what I was asking for! Someone to actually explain what was going on in the first place. If you can post your response as an answer I will mark it as such :) — , May 28 '19 at 00:37

score 0 · Answer 1 · answered May 27 '19 at 06:28

0

First of all don't allow the data to be inserted with spaces and special characters in your database,As it will open doors for any one to do SQL injections,

For avoiding data with spaces and special characters you can use MySQL_real_escape_string which is an inbuilt function of MySQL you can use this if you are using MySQL without pdo's.

IF you are using pdo object then you can surely use prepare statement for you query as prepare statement is more secure and optimise way to write your query as it provide an advantage of using named parameters in your query.

answered May 27 '19 at 06:28

Rohit Sawant

36
6

I am using GoDaddys php services with their MySql service as well. I do not have it setup with pdo's as of now. – May 28 '19 at 00:38
1

You do not need PDO to use prepared statements. You can use MySQLi too. Escaping is not the right way of preventing SQL injection. Use only prepared statements. Then there are no special characters and you do not need to worry about problems such as the one OP is having. – Dharman May 28 '19 at 02:37

score 0 · Accepted Answer · answered May 28 '19 at 06:11

The basic mistake here is treating non-numerical data, as if it was purely numeric.

and the full part number is "5-86510-53" but when it was inserted into the table it just came out to "-86558" in the DB”

That’s because you told the database to do the calculation 5 minus 86510 minus 53. This is textual data, not numeric data, so it needs to be enclosed in quotes, when inserted into an SQL query directly.

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

(Prepared statements solve this differently.)

MySQL and PHP not inserting rows with spaces or special characters

2 Answers2