PHP sql treat special characters

Question

$sql = "INSERT INTO golf (datum, odkud, odjezd ) VALUES ('$datum', '$odkud', '$odjezd')";
if(!mysqli_query($connection,$sql)) {
echo '<p class="error">Záznam nebyl vložen</p>';
} else {
header ("location :/golf");
}

Hello, I am working on my thesis to school. I have this code and my supervisor keeps telling me to "treat special characters". How do I do that? He only saw the code I showed you.

I think it's about validation. you run the query directly with variables without any validations. so, for security purposes you have to validate the data before run the query or else it will subject to security threas like sql injection. So, search how to validate the request — dilusha_dasanayaka, Apr 14 '18 at 12:17
How do I validate? I mean, I have date, time and name (of a city) if(empty($datum)) { $errorMessage .= "

Pedro Lobito · Answer 1 · 2018-04-14T14:04:11.863

User input MUST be sanitized prior to insertion, you'll be opening a door into your server otherwise.
You also need to validate the input. What I normally do, is create a series of regex to validate each field individually, or use one of the available php validate filters.
Remember, you can - and should - do client side validation, which is great to reduce server load, but has 0 value as a security measure because it can be easily faked. server side validation is the most important as it's your last line of defense.
Don't take user input lightly, tons of servers get hacked due to bad or nonexistent user input sanitization.

To directly answer your question, mysqli_real_escape_string() is your friend to escape special characters, i.e.:

$odjezd = mysqli_real_escape_string($connection, $odjezd)

Characters encoded are NUL (ASCII 0), \n, \r, \, ', ", and Control-Z.

Update:

I have used mysqli_real_escape_string and i am still able to submit "a{b}c'=%" I would like it to remove spec.characters and just input abc...how?

Let's assume that $odkud can only contain letters or digits and be 5 chars long only to validate, we can use preg_match() as validator, i.e.:

$id  = $_REQUEST['id']; 
if (preg_match('/^[a-z\d]{5}$/i', $odkud)) {
    # Successful match
} else {
    # Match attempt failed
}

Live Regex Example & Explanation

If you just need to remove the special characters use one of the php filters mentioned above or preg_replace, i.e.:

$odkud_filtered = preg_replace('/[^a-z\d]/i', '', $odkud);
# abc

Live Regex Example & Explanation

score 1 · Answer 2 · answered Apr 14 '18 at 12:45

1

your supervisor just ask you to treat special characters . For that @Pedro’s answer is enough.

$odjezd = mysqli_real_escape_string($connection, $odjezd)     // copied from @Pedro’s answer

But if you need more validation, you can check the format of the date is correct?

$test_arr  = explode('/', $POST[‘date’]);
if (count($test_arr) == 3) {
    if (checkdate($test_arr[0], $test_arr[1], $test_arr[2])) {
        // valid date ...
    } else {
        // problem with dates ...
    }
} else {
    // problem with input ...
}

Likewise you can validate your data according to your required way.

this might be helpful.

answered Apr 14 '18 at 12:45

dilusha_dasanayaka

1,401
2
17
30

I have $odjezd = mysqli_real_escape_string($connection, $_REQUEST['odjezd']); Is it the same as what Pedro says? – Martin Švejda Apr 14 '18 at 13:10
1

yes, but here you create a new variable called $objezd and assign the value return by mysqli_real_escape_string – dilusha_dasanayaka Apr 14 '18 at 13:13
So which one is corect? mine or pedros? – Martin Švejda Apr 14 '18 at 13:14
1

both. no big difference – dilusha_dasanayaka Apr 14 '18 at 13:18
I have used mysqli_real_escape_string and i am still able to submit "a{b}c'=%" I would like it to remove spec.characters and just input abc...how? – Martin Švejda Apr 14 '18 at 13:21

PHP sql treat special characters

2 Answers2