Php escape string with GET function issue with apostrophe

[PNewCode]

Hello. I have a tiny script that sends values to a database, which is grabbed from a different page that has values of the database.
 

On the first page, there are a list of values that are from the db.
Next to each line, there is a "send" button
This button sends those valuse of that row through a url string to a new page
The new page "Gets" those values and inserts them into a different place on the same database table (I don't know why, this doesn't make sense to me but I'm told it has to be this way and I can't change the structure of the db)

It all works fine, except times when there's an apostrophe

Here's a challenge I've been at for over a week now.
I've managed to do this just fine with various escape strings when posting. But since this is GET, all of those methods don't seem to work
Does anyone know a working method for this?

Also, I can't change each entty manually because these entries could change at any time through the day by various users within a circle of friends. So I would have to watch it 24/7 to correct each one haha.

Here is what I have and en example of the string url that gets sent to this is below it

NOTE: The groupno and linkno are not a factor here as it works fine and it has a reason of it's own. It works fine when the information has douple quotes or other characters. Just not a single quote or apostrophe
 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');


$servername = "localhost";
$username = "removed for posting";
$password = "removed for posting";
$dbname = "removed for posting";


// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);


// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}

    $group1 = $_GET['group1'];
    $group1 = str_replace("'", "''", $group1);
    $link1 = $_GET['link1'];
    $groupno = $_GET['groupno'];
    $linkno = $_GET['linkno'];


$sql = "UPDATE tourney
SET $groupno = '$group1', $linkno='$link1'
WHERE id=1";

if (mysqli_query($conn, $sql)) {
      echo " ";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}



mysqli_close($conn);
?>

Link Address example
https://www.thewebsite/test.php?link1=https://www.youtube.com/watch?v=7WOR-Yj4fOk&group1=musicvideo - Don't be angry - YouTube&groupno=group43&linkno=link43

[mac_gyver]

you need to use a prepared query in order to prevent any sql special characters in a value from being able to break the sql query syntax, which is how sql injection is accomplished. if it seems like the mysqli extension is overly complicated and inconsistent, especially when dealing with prepared queries, it is. this would be a good time to switch to the much simpler and better designed PDO extension.

converting an old query that puts the data values directly into the sql query statement into a prepared query using the PDO extension is straightforward -

1. remove, and keep for later, any php variables that are inside the sql query statement. note: any wild-card characters in a LIKE comparison are supplied as part of the data value
2. remove any quotes or {} around the value and any concatenation dots/extra quotes that were used to get the php variable into the sql query statement
3. put a simple ? prepared query place-holder into the sql query statement for each value
4. call the PDO prepare method for the sql query statement
5. call the PDOStatement execute method with an array of the variables you removed in step #1.
6. for a query that returns a result set, fetch the data from the query. see the fetch() method when fetching a single row of data. the fetchAll() method when fetching all the rows of data at once. and occasionally the fetchColum() method when fetching a single column from a single row of data. forget about any num rows function. just fetch then test if/how many rows of data there are.

here is typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions. this is the default setting now in php8+
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc, so that you don't need to specify it in each fetch statement
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

 

Edited August 29 by mac_gyver

[PNewCode]

@mac_gyver You are clearly brilliant. So much in fact that I think my brain exploded trying to translate what you said to laymans terms haha. I didn't really understand your instruction, though I tempted to use what you posted with my own connection information and that didn't resolve the issue. Also made it so it doesn't even work without special characters now. I'm feeling certain it's because you explained how to use what you posted and I just don't understand what any of that means. But below is what I tried. Also it returned a new error (below) which doesn't happen when sending a word without an opostrophe

Warning: Undefined array key "groupno" in win1-2.php on line 28
Warning: Undefined array key "linkno" in win1-2.php on line 29
 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

$DB_HOST = 'removed for posting'; // database host name or ip address
$DB_USER = 'removed for posting'; // database username
$DB_PASS = 'removed for posting'; // database password
$DB_NAME = 'removed for posting'; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions. this is the default setting now in php8+
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc, so that you don't need to specify it in each fetch statement
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);



    $group1 = $_GET['group1'];
    $link1 = $_GET['link1'];
    $groupno = $_GET['groupno'];
    $linkno = $_GET['linkno'];



$sql = "UPDATE tourney
SET $groupno = '$group1', $linkno='$link1'
WHERE id=1";



?>

 

[PNewCode]

Update @mac_gyver I missed changing the character set. So I did that and I get this

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [2019] Unknown character set in win1-2.php:18 Stack trace: #0 win1-2.php(18): PDO->__construct() #1 {main} thrown in win1-2.php on line 18

And line 18 is this

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);
 

and I get this with any test to send without or with an apostrophe

[jodunno]

1 hour ago, PNewCode said:

@mac_gyver You are clearly brilliant. So much in fact that I think my brain exploded trying to translate what you said to laymans terms haha. I didn't really understand your instruction, though I tempted to use what you posted with my own connection information and that didn't resolve the issue. Also made it so it doesn't even work without special characters now. I'm feeling certain it's because you explained how to use what you posted and I just don't understand what any of that means. But below is what I tried. Also it returned a new error (below) which doesn't happen when sending a word without an opostrophe

Warning: Undefined array key "groupno" in win1-2.php on line 28
Warning: Undefined array key "linkno" in win1-2.php on line 29
 

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');

$DB_HOST = 'removed for posting'; // database host name or ip address
$DB_USER = 'removed for posting'; // database username
$DB_PASS = 'removed for posting'; // database password
$DB_NAME = 'removed for posting'; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions. this is the default setting now in php8+
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // set default fetch mode to assoc, so that you don't need to specify it in each fetch statement
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);



    $group1 = $_GET['group1'];
    $link1 = $_GET['link1'];
    $groupno = $_GET['groupno'];
    $linkno = $_GET['linkno'];



$sql = "UPDATE tourney
SET $groupno = '$group1', $linkno='$link1'
WHERE id=1";



?>

 

switching to pdo is a wise decision but you forgot to switch your query code as well ($sql)

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

$sql = 'UPDATE tourney SET groupno = :group, linkno = :link WHERE id=1';
$conn = $pdo->prepare($sql);
$conn->execute(array(':group' => $group1, ':link' => $link1));

groupno = :group
groupno needs to be the name of the column in the database which is to be updated
likewise for linkno.

:group is a named placeholder for the $group1 variable (protects against sql injection attacks).
likewise for linkno.

 

report back.

[PNewCode]

@jodunno Thank you for that. I made those adjustments and I'm still getting an error. I should note that the groupno and linkno don't need to match the db because that is being translated in the url information. It works as long as there is no apostrophe in the values.

Uncaught PDOException: SQLSTATE[HY000] [2019] Unknown character set in win1-2.php:17 Stack trace: #0 win1-2.php(17): PDO->__construct() #1 {main} thrown in win1-2.php on line 17

And line 17 is
$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);
 

[Barand]

Does it work with

$DB_ENCODING = 'utf8';

 

[PNewCode]

4 minutes ago, Barand said:

Does it work with

$DB_ENCODING = 'utf8';

@Barand I tried that just a few minutes ago too. And sadly no, that just brings me back to the original posts errors

The charset I'm using is utf8mb4_unicode_ci now. I just updated the entire table to that from utf8mb3_general_ci

Edited August 29 by PNewCode

[jodunno]

let us clarify: tourney is your table name, correct?

$groupno = '$group1', $linkno='$link1' = you cannot update a variable with a variable.

you are supposed to be updating a column with new data.

you are supposed to select a db column by name, which is to be updated with the variable referenced to by the placeholder.

could you show us a bit more details about your database tables?

[PNewCode]

@jodunnocourrect tourney is the table name.
The reason why that information work for all the other values is because it's passing that information in the url. Below is an example url that works. The goupno and linkno are translated in this url to the database columns (in this example the columns are named group43 and link43) The reason I did this, is to have one page to use for all of the updates for 73 other entries. Below is a working example. Then I'll show the non working example

working example (and all other entries work without an apostrophe)
https://www.thewebsite/test.php?link1=https://www.youtube.com/watch?v=7WOR-Yj4fOk&group1=musicvideo - Dont be angry - YouTube&groupno=group43&linkno=link43

non working example
https://www.thewebsite/test.php?link1=https://www.youtube.com/watch?v=7WOR-Yj4fOk&group1=musicvideo - Don't be angry - YouTube&groupno=group43&linkno=link43

the word "Don't be angry" shows on the page as "Don't be angry" however in this link it translates to the ' instead of the apostrophe. I'm not sure why since in the database, it says "Don't" correctly, but it's passing it with that translation.

Then, when it gets to the next page (test.php) you get the code I gave above. But it only completes if there is no ' being sent

Edited August 29 by PNewCode

[PNewCode]

@mac_gyver @jodunno @Barand I solved it. It took me following up with you all to realize what the issue was. It was in SENDING the value. So I made the adjustment below with what was suggested in here and it works perfectly

In the URL I changed it to goup1=" . html_entity_decode(".$group22."..........

Good grief. I can't believe I spent over a week on this and thats all I had to do. Well, and make a better connection like you all stated too.

Thank you all so much. I appreciate your help a LOT!