-1

This snippet works just fine until it gets to a database entry with an apostrophe. I see that I need to escape these after I pull them. Being new to PHP I'm not sure what to do with all this info about PDO and "->" and mysqli_real_escape_string(). I'm a little confused by it all. How do I escape $team1rows and $team2rows so I can pass them back to my page? Thanks.

$team1rows = mysqli_num_rows(mysqli_query($connection,"SELECT * FROM $page WHERE vote = '$team1'"));
$team2rows = mysqli_num_rows(mysqli_query($connection,"SELECT * FROM $page WHERE vote = '$team2'"));

echo $team1rows . "|" . $team2rows;

The echo works fine until it hits an apostrophe.

Layne
  • 642
  • 1
  • 13
  • 32
  • 1
    PDO Prepared statements: http://us1.php.net/pdo.prepared-statements, mysqli_real_escape_string(): http://us1.php.net/mysqli_real_escape_string MySQLi Prepared Statements: http://php.net/manual/en/mysqli.prepare.php Random tutorial: http://forum.codecall.net/topic/44392-php-5-mysqli-prepared-statements/ – teynon Mar 13 '14 at 02:50
  • How did you come to the conclusion the tedious mysqli escaping function has anything to do with PDO? Which manual or tutorial did you trip over? – mario Mar 13 '14 at 02:52
  • @Tom Thanks for the links. It sounds like there's a better way to do this but I'm almost done with thsi project so would hate to have to start over. From the links, I tried the following with no success. $team1number = mysqli_real_escape_string($connection,$team1rows); – Layne Mar 13 '14 at 03:33
  • possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) (see the mysqli-related information if you don't wish to switch to PDO) – user2864740 Mar 13 '14 at 03:43
  • https://stackoverflow.com/questions/21873008/store-contents-of-page-in-mysql-database/21873057#21873057 – Engr Saddam Zardari Mar 13 '14 at 04:44

3 Answers3

0

Use PDO instead, the prepare statement prevents SQL injections, if used correctly. The mysqli_query is the old method that shouldn't be used any longer. See an example listed below:

<?php
$pdo = new PDO('sqlite:users.db');
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$stmt->bindParam(':id', $_GET['id'], PDO::PARAM_INT); // <-- Automatically sanitized by PDO
$stmt->execute();

Source: http://www.phptherightway.com/#databases

The -> means you're dealing with an object's method. So: $pdo is an object Then you can access the methods(functions) within it using the -> $pdo->prepare for example

Steve
  • 1,423
  • 10
  • 13
  • It sounds like I would need to start over if I wanted to switch to PDO. Unfortunately, the tutorial I'm following didn't use it and I'm barely holding on as it is. PDO on the next project is more-likely, thanks for the nudge. In the mean-time, $team1number = mysqli_real_escape_string($connection,$team1rows); didn't work either. – Layne Mar 13 '14 at 03:31
  • @Layne I can agree with not switching to PDO at this point (although I would recommend it for a new project), but mysqli *also* supports prepared statements and only requires minimal changes to the queries to use. – user2864740 Mar 13 '14 at 03:44
0

tldr; use placeholders (aka parameterized queries / prepared statements). This will eliminate all SQL Injection, including accidentally broken queries when the data contains apostrophes!

Since mysqli supports placeholders, there isn't a need to switch to PDO! I've left the code in the non-OOP mysqli syntax, although I recommend using the mysqli-object API. The following code does not perform any "escaping" - if such is done, it is merely an implementation detail.

# Create prepared statement, bind parameters - no apostrophe-induced error!
# - With placeholders there is need to worry about quoting at all
# - I recommend explicitly selecting columns
# - $page is NOT data in the query and cannot be bound in a placeholder
$stmt = mysqli_prepare($connection, "SELECT * FROM $page WHERE vote = ?");
mysqli_stmt_bind_param($stmt, "s", $vote);

# Execute prepared statement
$result = mysqli_stmt_execute($stmt);

# Use results somehow;
# Make sure check $result/execution for errors!
# (PDO is nice because it allows escalation of query errors to exceptions.)
$count = mysqli_num_rows($result);

Now, as per above, $page can't be bound in a placeholder because it relates to the query shape but is not data. One acceptable method to approach this particular case - if not redesigning the schema in general - is to use a whitelist approach. For instance,

$approvedPages = array("people", "tools", "pageX");
if (!in_array($page, $approvedPages)) {
   # Wasn't a known page - might have been something mischievous!
   # Choose default approved page or throw error or something.
   $page = $approvedPages[0];
}
Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
-1

I figured out what I was doing wrong. Amateur mistake on my part. I was trying to escape the results of the query. But in this case the result turns out to be the NUMBER of rows. So escaping the result wasn't the problem, since it was just a number. I was confused because that number was associated with a value that had an apostrophe in it. I thoguht So the string used in the query hadn't been escaped and that was causing it to fail, not the result.

So by FIRST escaping the variables I was using in the query like this...

$page = mysqli_real_escape_string($connection, $page);
$team1 = mysqli_real_escape_string($connection, $team1);
$team2 = mysqli_real_escape_string($connection, $team2);

That gave me good strings to use for the following queries.

$team1number = mysqli_num_rows(mysqli_query($connection,"SELECT * FROM $page WHERE vote = '$team1'"));
$team2number = mysqli_num_rows(mysqli_query($connection,"SELECT * FROM $page WHERE vote = '$team2'"));

echo $team1number . "|" . $team2number;

Again, I had it all backwards and was trying to escape the results. Noob move on my part but learned a lot thanks to this discovery. Thanks all.

Layne
  • 642
  • 1
  • 13
  • 32