-1

I have used the so-called cooperative LOCK successfully in MySQL in the past. I had custom functions in my PHP code that ACQUIRE LOCK, RELEASE LOCK and CHECK whether lock is in effect. For example, to lock, I did something like :

//code
"SELECT GET_LOCK( 'unique_string', -1 ) AS acquired"
// code
return ( $row['acquired'] == 1);

And to unlock, I did something like:

// code
"SELECT RELEASE_LOCK( 'unique_string' ) AS released"
// code
return ( $row['released'] == 1 );

And lastly, to check if lock is in effect, I did:

//code
"SELECT IS_USED_LOCK( 'unique_string' ) AS connection_id"
//code
return ( NULL != $row['connection_id'] );

In all these cases, I used PDO::query() method ( i.e. no prepared statement) since my SELECT statements do not contain any parameters

Now, there is a need to concatenate input from the user to unique_string. Is there a way to make the unique_string contain a placeholder so as to use PDO::prepare() and avoid possible SQL injection attack?

Thanks.

Stephen Adelakun
  • 784
  • 2
  • 7
  • 24
  • 2
    As far as I can tell it's a regular SELECT query ant here is no reason for a placeholder not to work in – Your Common Sense Mar 04 '20 at 11:54
  • @YourCommonSense, are you suggesting that injection attack cannot work in such case? For example, if I concatenate a variable to `unique_string` like `unique_string.$variable`. What if $variable contains `;DROP *.* --`? I do not know for sure, I just want to be on the safe side. – Stephen Adelakun Mar 04 '20 at 12:06
  • 2
    I am suggesting that **placeholders work in SELECT queries.** and have no idea where did you get anything else – Your Common Sense Mar 04 '20 at 12:15
  • @YourCommonSense, Oh my bad! I did not see the 'NOT' in your comment. My apologies. – Stephen Adelakun Mar 04 '20 at 12:17
  • @YourCommonSense, I have honestly looked at the question you linked to. I do not think this is a duplicate. Please take another look very closely and kindly reopen this qustion. – Stephen Adelakun Mar 04 '20 at 14:22
  • In a way - yes. You just invented a non-existent problem out of nowhere, so technically your question is rather off topic than duplicate. but given it boils down to "how to add a string literal to a SELECT query though a placeholder", the duplicate suits as well. – Your Common Sense Mar 04 '20 at 14:25

1 Answers1

1

There should be no issue at all in using a prepared statement, just use

$statement = PDO::prepare("SELECT GET_LOCK( ?, -1 ) AS acquired");
$statement->execute([$uniqueString]);

You can also used named params, would recommend looking into PDO's prepared statements docs.

ikyuchukov
  • 565
  • 3
  • 12
  • I was thinking that way too. I will implement your recommendation and accept your answer as necessary. – Stephen Adelakun Mar 04 '20 at 12:09
  • 1
    The placeholder must be unquoted, and the parameter must be passed as an array to `execute`, as already commented. – deceze Mar 04 '20 at 14:37
  • @deceze, please take a look at the syntax of the lock, the string is quoted, so why unquote the placeholder? Just want to know. Unfortunately, stackoverflow is a community where almost any sincere question is viewed as a challenge. Well, you may not be like that, but I am frustrated! – Stephen Adelakun Mar 04 '20 at 14:53
  • 3
    @Stephen Because that's how placeholders work: https://www.php.net/manual/en/pdo.prepared-statements.php. It doesn't work like search-and-replace in strings. The database understands *on a syntactical level* that `?` is a placeholder. `'?'` is a string with a question mark in it, `?` is a placeholder. – deceze Mar 04 '20 at 14:55
  • @deceze, Got that. Could one however use a named placeholder in this case, when GET_LOCK() requires param 1 to be a string? Notice how the reason for my question is playing out – Stephen Adelakun Mar 04 '20 at 15:24
  • 2
    @Stephen Nope, not seeing what's playing out here. If you pass a string as the value for that placeholder, then it'll be a string. `SELECT ?` + `execute(['foo'])` → equivalent to `SELECT 'foo'`. Whether you use *named* placeholders or *positional* placeholders (`?`) is irrelevant. – deceze Mar 04 '20 at 15:26