Use string from form as parameter in stored procedure (SQL Server)

Question

I've been trying to follow the example from this question (Insert Stored Procedure and other variables into a temp table) and this one (Dynamic sql stored procedure update query issue?) to get this working but no joy as yet. I've been dipping my toe into more complex SQL queries (still learning) and would love some advice/guidance on this one.

I am trying to take a string from a form submitted on a webpage (PHP) to go into a where clause for an update query in a stored procedure. The end goal is to alter all the provided reference numbers to a statusrefno of 4 (which would mark them as closed rental products)

Here is the stored procedure in full

ALTER PROCEDURE [dbo].[TestingPPMRemovals]
    @EditedBy varchar (25),
    @PPMsList varchar (max)
AS
BEGIN
    SET NOCOUNT ON;

    CREATE table #tempPPMs (PPMs varchar(max))
    INSERT INTO #tempPPMs (PPMs) VALUES (@PPMsList)

    INSERT INTO PPMsEdit_AuditTrail (EditedBy, EditDate, PPMsEdited) 
    VALUES (@EditedBy, getdate(), @PPMsList)

    UPDATE jobs 
    SET StatusRefNo = 4, EditUserRefNo = 1114 
    WHERE WebReference IN (SELECT * FROM #tempPPMs) 

END

The list of these web references would look something like this in a query

UPDATE jobs 
SET StatusRefNo = 4, EditUserRefNo = 1114 
WHERE WebReference IN ('PPM-372046', 'PPM-372053', 'PPM-372072', 'PPM-372076', 'PPM-372077', 'PPM-372078')

I understand that the 'IN' is not compatible with a stored procedure and while the risk of SQL injection is very low because this webpage will only be accessible locally, I would like to negate that risk as much as possible.

The alternative way I tried to do this query did not work either which is here -

INSERT INTO PPMsEdit_AuditTrail (EditedBy, EditDate, PPMsEdited) 
VALUES (@EditedBy, getdate(), @PPMsList)

UPDATE jobs 
SET StatusRefNo = 4, EditUserRefNo = 1114 
WHERE WebReference IN (
   SELECT Top 1 PPMsEdited 
   FROM PPMsEdit_AuditTrail 
   ORDER BY EditDate desc
)

Here is the relevant section from my PHP in case it is needed for context -

$sqlsrv = sqlsrv_connect($serverName, $connectionInfo) or die(sqlsrv_error($sqlsrv));
$update = false;
$EditedBy = '';
$PPMsList = '';
$tsql = 'exec TestingPPMRemovals ?, ?';


if (isset($_POST['update'])){
    $PPMsList = $_POST['PPMsList'];
    $EditedBy = $_POST['EditedBy'];    
    
    $params = array($PPMsList, $EditedBy);
    $result = sqlsrv_query($conn, $tsql, $params);
    if ($result === false) {
        die( print_r( sqlsrv_errors(), true) );
        $response = array('response'=>'notok', 'data'=>'loyo');
        $serverresponse = json_encode($response);
    } else {
        $row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC);
        $response = array('response'=>'ok', 'data'=>$row[0]);
        $serverresponse = json_encode($response);
    }
    sqlsrv_free_stmt($result);
} else {
    $response = array('response'=>'notok', 'flag'=>$flag, 'data'=>'cc');
    $serverresponse = json_encode($response);
}

echo ($serverresponse);
    
    $_SESSION['message'] = "This information has been succesfully updated";
    $_SESSION['msg_returndate'] = "info";
    
    header("location: ../success.php");

I had based that PHP code on a lesson from Dani Krossing's PHP Youtube tutorials (https://www.youtube.com/watch?v=5wGDu-aigZs&list=PL0eyrZgxdwhwBToawjm9faF1ixePexft-&index=33)

Any advice or resources to help me get this working would be great. I have spent a good chunk of time looking for another post that would help but haven't found any.

Thanks

You make assumptions that are incorrect. Start by reading Erland's discussion on arrays and lists. Bookmark his site - lots of useful information — SMor
– SMor, Commented Mar 23, 2021 at 17:13
Unfortunately it appears PHP does not suppport Table Valued Parameters, which is what you really need. I would say hack around it with XML or JSON. — Charlieface
– Charlieface, Commented Mar 23, 2021 at 18:54

Alan Burstein · Accepted Answer · 2021-03-23 19:04:50Z

1

I am trying to take a string from a form submitted on a webpage (PHP) to go into a where clause for an update query in a stored procedure.

For this you need a splitter (AKA tokenizer). If you are using SQL 2016+ you have STRING_SPLIT. For earlier versions you can grab this. With @WebReferenceNumbers as the "submitted string" your SQL would look like this:

DECLARE @WebReferenceNumbers VARCHAR(8000) =
  'PPM-372046,PPM-372053,PPM-372072,PPM-372076,PPM-372077,PPM-372078'

-- SELECT | UPDATE | WHATEVER WHERE... IN
SELECT s.[value]
FROM   STRING_SPLIT(@WebReferenceNumbers,',') AS s;

There may be a little cleanup such as whitespace trimming required based on what the string looks like but this is a simple task.

answered Mar 23, 2021 at 19:04

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pc90 Over a year ago

Thank you @Alan-Burstein for your help! Greatly appreciated

Collectives™ on Stack Overflow

Use string from form as parameter in stored procedure (SQL Server)

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related