Can i run functions after SELECT * FROM db WHERE in php?

Question

I want to use a function after WHERE

like this it works

$sql = "SELECT * FROM Prodotti WHERE Id=10";

what if i want the id to be in the URL link? the link example is this: https://www.try.org/product.php?signup=98 this way it's not working

$sql = "SELECT * FROM Prodotti WHERE strpos($fullUrl, signup=Id)";

@IslamElshobokshy whilst this is valid, it's open to SQL injection — treyBake, Jun 18 '18 at 14:41
@IslamElshobokshy You probably should not recommend that as it's extremely vunerable to SQL injection. — War10ck, Jun 18 '18 at 14:41
Please don't directly pass the value. Use prepared statements and validate the input. — Script47, Jun 18 '18 at 14:41
See this article: [PHP, The Right Way](http://www.phptherightway.com/) particularly the part about the _PDO_ extension and Parameterized SQL Queries... — War10ck, Jun 18 '18 at 14:41
Can you tell us what exactly you want to do? Why do you want to use `strpos` for an ID? — Nico Haase, Jun 18 '18 at 14:43
i want the user to visit a link (he gets from a qr code) which is connected to the Id of a product. I won't use get i know sql injection, it can make possible to delete everything — Luca Agostinelli, Jun 18 '18 at 14:46
Then why do you use `strpos` in your query? Why not use the ID from the URL? — Nico Haase, Jun 18 '18 at 14:50
because with strpos i can get the number from the url, and compare it with the Id in a query. what do you mean ID from the url? — Luca Agostinelli, Jun 18 '18 at 14:56

score 0 · Accepted Answer · edited Jun 18 '18 at 15:48

0

You can get the id by using the $_GET superglobal:

$id = (int) $_GET['signup']; // (int) makes sure it is an integer and no string

Now in order to make it work within your query you first need to make the input secure. You can make an input secure by using mysqli_real_escape_string but since you need an integer and not a string it is better to use a prepared statement.

In your query you can than do

$sql = "SELECT * FROM `Prodotti` WHERE `Id` = $id";

Use backticks around table and column names to prevent mysql reserved word error.

Example of prepared statement:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$id = (int) $_GET['signup']; 
if ($stmt = $mysqli->prepare("SELECT * FROM `Prodotti` WHERE `Id` = ?")) {

    /* bind parameters for markers */
    $stmt->bind_param("i", $id);// i for integer s for string

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($district);

    /* fetch value */
    $stmt->fetch();

    // Do something with the fetched data

    /* close statement */
    $stmt->close();
}

edited Jun 18 '18 at 15:48

Script47

14,230
4
45
66

answered Jun 18 '18 at 14:47

SuperDJ

7,488
11
40
74

This is terrible advice for securing, the hint is in the function name `mysqli_real_escape_**string**`. – Script47 Jun 18 '18 at 14:48
@Script47 that is whay is said it is better to use prepared statements – SuperDJ Jun 18 '18 at 14:49
But why would you suggest incorrect code in the first place? – Script47 Jun 18 '18 at 14:49
@Script47 it might be incorrect for this example but it is good to know for the OP in general to secure user inputs. It is also good to know the existence of different methods – SuperDJ Jun 18 '18 at 14:50
Definitely good to know different methods but not when they are being used incorrectly, besides, escaping is pointless with prepared statements. – Script47 Jun 18 '18 at 14:51
how can i use prepared statements? – Luca Agostinelli Jun 18 '18 at 14:52
'*it might be incorrect for this example*' - But you are answering *this* question so you should be giving correct information. – Script47 Jun 18 '18 at 14:52
@Script47 better now? – SuperDJ Jun 18 '18 at 14:55
@SuperDJ no, you are misunderstanding the purpose of that function, it doesn't make anything secure. It simply escapes a string. – Script47 Jun 18 '18 at 14:57
@Script47 thus making a string secure for use in a query – SuperDJ Jun 18 '18 at 15:00
@SuperDJ https://stackoverflow.com/questions/32391315/is-mysqli-real-escape-string-enough-to-avoid-sql-injection-or-other-sql-attack – Script47 Jun 18 '18 at 15:03
well using (int) solves everything because only numbers will pass through – Luca Agostinelli Jun 18 '18 at 15:07
@Script47 I know it isn't a 100% "secure" but it is better to have some escaping/ "security" than not all. That being said nothing is a 100% secure – SuperDJ Jun 18 '18 at 15:09
i just need little security that's enough for me – Luca Agostinelli Jun 18 '18 at 15:12
@LucaAgostinelli what about for your users? Are they happy with a "little"? – Script47 Jun 18 '18 at 15:12
I'm a student and this project is not going online – Luca Agostinelli Jun 18 '18 at 15:13
@SuperDJ '*That being said nothing is a 100% secure*' - That seems like a case of whataboutism. – Script47 Jun 18 '18 at 15:13
@LucaAgostinelli then learn the best while you are learning. Don't settle for average. If you aren't doing the best now what makes you think you will be doing the best when you have work deadlines? – Script47 Jun 18 '18 at 15:14

score -3 · Answer 2 · answered Jun 18 '18 at 14:42

-3

You can get the signup parameter from the url like this:

$signup = $_GET['signup'];

and then use it in your query:

$sql = "SELECT * FROM Prodotti WHERE Id = '$signup'";

but this is not secure, i suggest you also google for "php mysql prepared statements"

answered Jun 18 '18 at 14:42

Eugen Bogdanovich

211
2
5

2

Why not just suggest the preferred way instead of showing bad code examples? – Script47 Jun 18 '18 at 14:42
2

While this solution works, it is extremely vunerable to SQL injection. Consider revising this example to provide additional security... – War10ck Jun 18 '18 at 14:43

Can i run functions after SELECT * FROM db WHERE in php?

2 Answers2